Compare Values in Multiple Columns and return a result

**edbhome** · 03-17-2009, 04:13 PM

I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.

I have a column between the years that calculates the percent of gross revenue for the specific department/cost.

I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.

The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.

Any help would be appreciated.

Thanks in advance,

Ed

**NBVC** · 03-18-2009, 08:40 AM

Perhaps something like this?

=IF(OR(B2-A2>A1,C2-B2>$X$1),"check me out","")

where A2, B2 and C2 contain your 3 year values.. and X1 contains the trigger value...

so if B2 is greater that A2 or C2 is greater than B2 by more that the value in X1 you will get the "check me out" result.

formula can be copied down.

**edbhome** · 03-18-2009, 09:05 AM

Thanks for the quick reply. It's very close to what I'm trying to do and I appreciate the formula.

Question ... is there a way to have two trigger values so if it's trigger "A" it's "Check me out" and if it's trigger "B" it's "Warning"?

**NBVC** · 03-18-2009, 09:13 AM

You mean?

=IF(OR(B2-A2>$X$1,C2-B2>$X$1),"check me out",IF(OR(B2-A2>$Y$1,C2-B2>$Y$1),"Warning",""))

where X1 and Y1 hold the respective Triggers.

Note: You may need to rearrange the to OR() statements depending on which trigger you want to be evaluated first... the formula will evaluate the first IF first and stop if it is TRUE without checking the second OR() statement.

**edbhome** · 03-18-2009, 09:18 AM

Outstanding.

If I follow the logic here ... I can do up to 7 nested if statements (trigger values) so long as each criteria being tested preceeds the next as a false result until it finds a positive value.

**NBVC** · 03-18-2009, 09:25 AM

Correct.

Although, if you need more than 7, you can revert to other workarounds... like having a lookup table and using the Vlookup() function, etc... or you can upgrade to 2007, where you are allowed I think up to 64 nested functions.

**edbhome** · 03-18-2009, 09:28 AM

Excellent.

Thank you very much for the help and while I'm curious as to how a vlookup would work in this scenario, this will do for now since the triggers are pretty simple. Just trying to quickly find the "gross" exceptions.

Again,

Thanks,

Ed

**NBVC** · 03-18-2009, 09:37 AM

The Vlookup() method would be something like this...

you would set up a small table on the side.. something like:

0 good
50000 warning
75000 check me out
100000 danger

say in X1:Y4

Then use formula: =VLOOKUP(MAX(B2-A2,C2-B2),$X$1:$Y$4,2) to get corresponding comment.