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.

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

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

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

in G1
=AVERAGE(D1,E1,F1)


hope this helps.



"cabana_boy" <u43066@uwe wrote in message news:830596d49ce14@uwe...
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.

corrections

=IF(A1="","",IF(A1<=631107002.36,2,IF(AND(A163110 7002.36,A1<=787270260.17),1,IF(A1787270260.17,3," "))))

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

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

=AVERAGE(D1,E1,F1)


"Gaurav" wrote in message
...
in D1
=IF(A1<=631107002.36,2,IF(AND(A1631107002.36,A1<= 787270260.17),1,IF(A1787270260.17,3,"")))

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

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

in G1
=AVERAGE(D1,E1,F1)


hope this helps.



"cabana_boy" <u43066@uwe wrote in message news:830596d49ce14@uwe...
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.

Awsome!!! Thanks so much!!!

Gaurav wrote:
in D1
=IF(A1<=631,107,002.36,2,IF(AND(A1631,107,002.36 ,A1<=787,270,260.17),1,IF(A1787,270,260.17,3,"")) )

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

in F1
=IF(C1<=529,2,IF(AND(C1529,C1<=738),1,IF(C1738, 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.

Why are you doing all those fruitless extra tests, Gaurav?
What's wrong with
=IF(A1="","",IF(A1<=631107002.36,2,IF(A1<=78727026 0.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))) ?
--
David Biddulph

"Gaurav" wrote in message
...
corrections

=IF(A1="","",IF(A1<=631107002.36,2,IF(AND(A163110 7002.36,A1<=787270260.17),1,IF(A1787270260.17,3," "))))

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

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

=AVERAGE(D1,E1,F1)


"Gaurav" wrote in message
...
in D1
=IF(A1<=631107002.36,2,IF(AND(A1631107002.36,A1<= 787270260.17),1,IF(A1787270260.17,3,"")))

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

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

in G1
=AVERAGE(D1,E1,F1)


hope this helps.



"cabana_boy" <u43066@uwe wrote in message news:830596d49ce14@uwe...
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.

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.


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Why are you doing all those fruitless extra tests, Gaurav?
What's wrong with
=IF(A1="","",IF(A1<=631107002.36,2,IF(A1<=78727026 0.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))) ?
--
David Biddulph

"Gaurav" wrote in message
...
corrections

=IF(A1="","",IF(A1<=631107002.36,2,IF(AND(A163110 7002.36,A1<=787270260.17),1,IF(A1787270260.17,3," "))))

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

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

=AVERAGE(D1,E1,F1)


"Gaurav" wrote in message
...
in D1
=IF(A1<=631107002.36,2,IF(AND(A1631107002.36,A1<= 787270260.17),1,IF(A1787270260.17,3,"")))

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

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

in G1
=AVERAGE(D1,E1,F1)


hope this helps.



"cabana_boy" <u43066@uwe wrote in message news:830596d49ce14@uwe...
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.