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

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

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:

A5B5 & A5C5 = 4
A5B5 & A5<C5 = 2
A5<B5 & A5<C5 = 0
A5=B5 & A5C5 = 3
A5B5 & A5=C5 = 3
A5<B5 & A5=C5 = 1
A5<B5 & A5C5 = 2
A5=B5 & A5<C5 = 1
A5=B5 & A5=C5 = 2

"JulieD" wrote:

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

Basic,

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

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

HTH,
Bernie
MS Excel MVP


"basic" wrote in message
...
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:

A5B5 & A5C5 = 4
A5B5 & A5<C5 = 2
A5<B5 & A5<C5 = 0
A5=B5 & A5C5 = 3
A5B5 & A5=C5 = 3
A5<B5 & A5=C5 = 1
A5<B5 & A5C5 = 2
A5=B5 & A5<C5 = 1
A5=B5 & A5=C5 = 2

"JulieD" wrote:

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(A5
C5,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

And this is equivalent, and possibly a little clearer:

=(A5B5)*1+(A5=B5)*1+(A5C5)*1+(A5=C5)*1

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Basic,

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

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

HTH,
Bernie
MS Excel MVP


"basic" wrote in message
...
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:

A5B5 & A5C5 = 4
A5B5 & A5<C5 = 2
A5<B5 & A5<C5 = 0
A5=B5 & A5C5 = 3
A5B5 & A5=C5 = 3
A5<B5 & A5=C5 = 1
A5<B5 & A5C5 = 2
A5=B5 & A5<C5 = 1
A5=B5 & A5=C5 = 2

"JulieD" wrote:

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(A5
C5,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

I think this is right...

=SIGN(A5-B5)+SIGN(A5-C5)+2

--
Dana DeLouis
Win XP & Office 2003


"basic" wrote in message
...
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:

A5B5 & A5C5 = 4
A5B5 & A5<C5 = 2
A5<B5 & A5<C5 = 0
A5=B5 & A5C5 = 3
A5B5 & A5=C5 = 3
A5<B5 & A5=C5 = 1
A5<B5 & A5C5 = 2
A5=B5 & A5<C5 = 1
A5=B5 & A5=C5 = 2

"JulieD" wrote:

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

Nice one, Dana!

On Thu, 7 Apr 2005 23:37:41 -0400, "Dana DeLouis"
wrote:

I think this is right...

=SIGN(A5-B5)+SIGN(A5-C5)+2