How to compare 3 numbers and return value

B

basic

I am trying to compare 1 number to 2 other numbers and return a seperate
number based on the results of the comparison.

Example:
Sum cells A1:A4
Compare to Sum of cells B1:B4 and Sum of cells C1:C4.
If A1:A4>B1:B4 & C1:C4 then return value of 4
If A1:A4<B1:B4 & C1:C4 then return value of 0
If A1:A4>B1:B4 & A1:A4<C1:C4 then return value of 2
If A1:A4=B1:B4 & A1:A4=C1:C4 then return value of 3

I have 12 different combinations to compare and a different value for each
result. I have tried the IF(AND function but this limits me to 7 nested IF
statements.
This is driving me nuts, Please help me find another way!

Thanks
 
J

JulieD

Hi Basic

i must have missed an option, because i can only come up with 9 variations
and not 12 -

comparison.:.return value in formula below
A>B&A>C : 1
A>B&A=C : 2
A>B&A<C : 3
A=B&A>C : 4
A=B&A=C : 5
A=B&A<C : 6
A<B&A>C : 7
A<B&A=C : 8
A<B&A<C : 9

and to make the formula easier to read i put the sum of each range in row 5
(e.g. A5 has =SUM(A1:A4) - (but you could substitute A5 in the formula below
with SUM(A1:A4) if you wish))

I think this covers all of the above combinations

=IF(A5>B5,IF(A5>C5,1,IF(A5=C5,2,3)),IF(A5=B5,IF(A5>C5,4,IF(A5=C5,5,6)),IF(A5>C5,7,IF(A5=C5,8,9))))
 
B

basic

Sorry JulieD-
I guess I should have put all the variations into the question. I thought I
could just add them in but I guess I must have overestimated myself--again.
Here are ALL the variations:

A5>B5 & A5>C5 = 4
A5>B5 & A5<C5 = 2
A5<B5 & A5<C5 = 0
A5=B5 & A5>C5 = 3
A5>B5 & A5=C5 = 3
A5<B5 & A5=C5 = 1
A5<B5 & A5>C5 = 2
A5=B5 & A5<C5 = 1
A5=B5 & A5=C5 = 2
 
B

Bernie Deitrick

Basic,

When A5 is > is worth 2, and = is worth 1, so simply use:

=IF(A5>B5,2,IF(A5=B5,1,0))+IF(A5>C5,2,IF(A5=C5,1,0))

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

And this is equivalent, and possibly a little clearer:

=(A5>B5)*1+(A5>=B5)*1+(A5>C5)*1+(A5>=C5)*1

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top