Help with Excel formula to compare 1 column of numbers to other columns.
July 19, 2016 7:37 AM   Subscribe

I have an excel sheet with 4 columns of numbers. The first column is a recent donation amount. The other three columns were the prompts of donation amounts, (the Reach, the Target, and the Anchor). Is there an excel formula that can compare these four columns?

I would like to use a formula to compare the donation amount to the three other columns and output results that would say the donation was, Above Reach, Equals Reach, Between R and T, Equals Target, Between T and A, Equals Anchor, and Below Anchor. Is there an easy way to do this?
posted by andoatnp to Work & Money (8 answers total)
 
It's not pretty, but a series of IF commands will do that:

=IF(A1>=B1, "ABOVE REACH", IF(A1>=C1, "BETWEEN R AND T",IF(A1>=D1, "ABOVE ANCHOR", "BELOW ANCHOR")))

(I assumed equals value is same as above, but you can add more conditions to have those output). My fake data looked like:


6500 6000 3000 1000 ABOVE REACH
3000 6000 3000 1000 BELOW REACH ABOVE TARGET
2500 6000 3000 1000 ABOVE BASE
posted by parkerjackson at 7:51 AM on July 19, 2016


Best answer: You can do if statements in excel. They get a little funky when they're nested, but you can do something like:
=IF(A2>B2, "Above Reach", 
IF(A2=B2, "Equals Reach", 
IF(A2>C2,"Between T and R",
IF(A2=C2,"Equals Target", 
IF(A2>D2, "Between A and T", 
IF(A2=D2, "Equals anchor", 
"Below anchor")))))))
I did that on my phone so I'm not sure i have the right number of closing parentheses, but it's the right idea.
posted by brainmouse at 8:02 AM on July 19, 2016


Response by poster: I've got a simplified test version running with just two conditions to make sure I understand how the if statements work, based on parkerjackson's answer. What is the rule in excel for an if statement that uses a greater than and less than condition at the same time? For my "Between Reach and Target" condition, I need to do basically this if(A1 is less than B1 and A1 is greater than C1) "Between R and T".

Both of your examples seem to be using a single greater than or less than sign, when I am trying to capture if the donation amount falls between two different numbers. Does that makes sense?

Thanks for the help so far, I've got a simple version working, I just need to specify when the donation falls between two different columns.
posted by andoatnp at 8:06 AM on July 19, 2016


So you can use my formula to do that, since what it's doing is eliminating one condition at a time. First it sees if it's bigger than the reach. If it's not it sees if it is equal to the reach. If it's not it sees if it's bigger than the target, but if it is, it already knows it's smaller than the target and so you know it has to be between them.
posted by brainmouse at 8:10 AM on July 19, 2016


Best answer: That said though, if you want to be more specific about it, you can put multiple things in an IF statement using a nested AND statement:
=IF(AND(A2=10,B2=12),"they equal 10 and 12","they don't")

posted by brainmouse at 8:28 AM on July 19, 2016


Yes, as brainmouse says, you want to only work in one direction at a time, and give an answer _and stop_ if it falls in that "bucket", or if it doesn't, then move on to the next.

The IF statement in Excel goes like: [conditional], [do this if true], [do this if false]. So, you want to nest them as in the example.

You can think about it this way:

Conditional: If Amount > Reach (this is the conditional),
If true: "Above Reach" (if it's true, the formula will print this _and stop here_),
If false (another conditional): If Amount > Target (if amount was less than reach, here's another conditional),
If true (for second conditional): "Between Target and Reach" (if amount was greater than reach, you wouldn't get to this step; if amount is less than reach but greater than target, this will print, _and stop_),
If false (for second conditional, another conditional): If Amount = Target (if amount was less than reach and less than target, here's another conditional)
… and so on

Also, you probably need one fewer conditional than you might realize. For the below anchor one, you don't need to test whether it's less than, you just use the "if false" bit. That's why the example given doesn't have a less than symbol at the end: the last conditional is saying "is it equal to the anchor? if yes, print "equals anchor", if not print "below anchor".
posted by brentajones at 8:32 AM on July 19, 2016


Another way to do this (depending on if you really need the text that you mentioned) would be with conditional formatting. That could (for example) make the color of the "Recent Donation" cell change based on which of the categories that donation amount falls in. Green, yellow, red or some other readable spectrum works well if the purpose is a visual review.
posted by milqman at 10:03 AM on July 19, 2016


Response by poster: Thanks all. I have the formula from brainmouse at 8:02 working, and I'm going to play around with the version from 8:28 as well, just so I make sure I understand it, and to make sure the results are equivalent.
posted by andoatnp at 11:12 AM on July 19, 2016


« Older Data recovery with pirated content   |   Domestic cleaning agencies in central London... Newer »
This thread is closed to new comments.