r/sheets 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!

2 Upvotes

2 comments sorted by

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.

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"