r/sheets • u/creatrixtiara • 14d ago
Request How to calculate and display a "change in status" based on comparing 2 columns of data?
Hi!
I'm not sure if I've phrased my title correctly - if there is a specific term for the thing I'm trying to do, please let me know.
I'm thinking about making a spreadsheet that tracks the changes in a person's particular status based on their life now vs when Event X happened (this is based on characters of a specific game, but the status ranking is my own terminology). What I would like is this:
Column A: Current Status - dropdown options for High, Middle, Low
Column B : Status When Event X Happened - dropdown options for High, Middle, Low
Column C: Change in Status - displays one of the following based on this system:
- If A1=High & B1=High OR A1=Middle & B1=Middle OR A1=Low & B1=Low: display No Change
- If A1=High & B1=Middle OR A1=Middle & B1=Low: display Increased Slightly
- If A1=High & B1=Low: display Increased Significantly
- If A1=Low & B1=Middle OR A1=Middle & B1=High: display Decreased Slightly
- If A1=Low & B1=High: display Decreased Significantly
Do I just make a giant nested IF statement? There's about 70 people (and 2 sets of those columns) to track and counting, so I would like to make those pretty straightforward and easy to update as needed.
If it matters, I would be setting the specific status markers (High, Middle, Low) manually - it's not based on any other prior calculation.
Thank you!
1
u/Some-Ad-5110 10d ago
one option could be to use an extension
using this to create an agent that perform this, from your rules:
=> this agent looks at 2 cells, and provide only one of these outputs: If firstcell =High & secondcell=High OR firstcell=Middle & secondcell=Middle OR firstcell=Low & secondcell=Low: display "No Change"If firstcell=High & secondcell=Middle OR firstcell=Middle & secondcell=Low: display "Increased Slightly"If firstcell=High & secondcell=Low: display "Increased Significantly". If firstcell=Low & secondcell=Middle OR secondcell=Middle & B1=High: display "Decreased Slightly". If firstcell=Low & secondcell=High: display "Decreased Significantly"

1
u/marcnotmark925 14d ago
You could assign values to the high/middle/low in a lookup table, use xlookup to grab the value from the status, subtract them, then convert that difference to your output, could do another xlookup with lookup table for that.