Help with a conditional formula using ranges

[cabana_boy]

I need help creating a conditional formula and I can't find anything out
there for my specific situation. I'll break this out into two seperate parts
for understandability. In cell a1 I have the value 908,135,661.64. I want to
create a conditional formula that states if the value of a1 is from 0 to 631,
107,002.36 then return a value of 2. If the value is from 631,107,002.37 to
787,270,260.17 then return a value of 1. If the value is greater than 787,270,
260.17 then return a value of 3.

The second part of this is that I have a value of 246 in cell b1 and 660 in
c1. For cell b1 I want to give a conditional formula that states values from
0 to 147 return a value of 2, 148 to 214 returns 1, and 215 and greater
returns 3. For cell c1, values from 0 to 529 returns 2, values from 530 to
738 returns 1, and values 738 and greater returns 3. Once I get the values
for cells a1, b1, and c1 I am going to average the three together. It would
be nice if I could create a long formula and do the entire calculation in one
step, but if I need to create three seperate formulas and calculate the
average in another cell that would be acceptable.

The average part is going to be easy (I think), but where I'm having trouble
is coming up with the conditional formula. I've been able to start a
conditional formula that will return a true or false value if the range for
cell a1 is between 631,107,002.37 to 787,270,260.17, but I think I may be
going down the wrong road because I can't get it to return the correct value.

 

[Gaurav]

in D1
=IF(A1<=631,107,002.36,2,IF(AND(A1>631,107,002.36,A1<=787,270,260.17),1,IF(A1>787,270,260.17,3,"")))

in E1
=IF(B1<=147,2,IF(AND(B1>147,B1<=214),1,IF(B1>214,3,"")))

in F1
=IF(C1<=529,2,IF(AND(C1>529,C1<=738),1,IF(C1>738,3,"")))

in G1
=AVERAGE(D1,E1,F1)


hope this helps.

 

[Gaurav]

corrections

=IF(A1="","",IF(A1<=631107002.36,2,IF(AND(A1>631107002.36,A1<=787270260.17),1,IF(A1>787270260.17,3,""))))

=IF(B1="","",IF(B1<=147,2,IF(AND(B1>147,B1<=214),1,IF(B1>214,3,""))))

=IF(C1="","",IF(C1<=529,2,IF(AND(C1>529,C1<=738),1,IF(C1>738,3,""))))

=AVERAGE(D1,E1,F1)

 

[cabana_boy]

Awsome!!! Thanks so much!!!

in D1
=IF(A1<=631,107,002.36,2,IF(AND(A1>631,107,002.36,A1<=787,270,260.17),1,IF(A1>787,270,260.17,3,"")))

in E1
=IF(B1<=147,2,IF(AND(B1>147,B1<=214),1,IF(B1>214,3,"")))

in F1
=IF(C1<=529,2,IF(AND(C1>529,C1<=738),1,IF(C1>738,3,"")))

in G1
=AVERAGE(D1,E1,F1)

hope this helps.

I need help creating a conditional formula and I can't find anything out
there for my specific situation. I'll break this out into two seperate

[quoted text clipped - 31 lines]

going down the wrong road because I can't get it to return the correct
value.

 

[David Biddulph]

Why are you doing all those fruitless extra tests, Gaurav?
What's wrong with
=IF(A1="","",IF(A1<=631107002.36,2,IF(A1<=787270260.17,1,3)))
=IF(B1="","",IF(B1<=147,2,IF(B1<=214,1,3)))
=IF(C1="","",IF(C1<=529,2,IF(C1<=738,1,3))) ?

 

[Gaurav]

May be fruitless but thats what i could think of when nobody replied to the
post and it did help the OP.

Thanks for the enlightenment, David. I appreciate it.