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
AB&AC : 1
AB&A=C : 2
AB&A<C : 3
A=B&AC : 4
A=B&A=C : 5
A=B&A<C : 6
A<B&AC : 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(A5B5,IF(A5C5,1,IF(A5=C5,2,3)),IF(A5=B5,IF(A5 C5,4,IF(A5=C5,5,6)),IF(A5C5,7,IF(A5=C5,8,9))))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"basic" wrote in message
...
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:A4B1:B4 & C1:C4 then return value of 4
If A1:A4<B1:B4 & C1:C4 then return value of 0
If A1:A4B1: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