I'm trying to write an if statment on 4 different pieces of data that gives
them a value of 1, 2, or 3 based on their values. The first set of data has a
range of -5,000,000 to 2,000,000, the second set of data ranges from 0 to 150,
the third set ranges from 0 to 140, the fourth set ranges from 0 to 130.

For the first set of data (b2), I need to assign a value of 1 for data
ranging from -821,524.12 to 866,503.73. I need to assign a value of 2 for
data ranging from -912,804.58 to -821,524.12 and 866,503.73 to 962,781.92. I
need to assign a value of 3 for values less than -912804.58 and greater than
962,781.92.

Ranges for the second set (c2) are 0 to 84 gets a value of 1, 85 to 94 gets a
value of 2, 95 and up gets a value of 3.

Ranges for the third set (d2) are 0 to 47 gets a value of 1, 48 to 53 gets a
value of 2, 54 and up gets a value of 3

Ranges for the fourth set (e2) are 0 to 18 gets a value of 3, 19 to 21 gets a
value of 2, 21 and up gets a value of 1 (note this is opposite from the
previous two data sets)

I'm taking the results and averaging them, so the formula I've come up with
is:

=(IF(AND(B2<866503.73,B2-821524.12),1,IF(AND(B2=866503.73,B2<=962781.92),2 ,
IF(AND(B2=-912804.58,B2<=-821524.12),2,IF(AND(B2<-912804.58,B2962781.92),3,
""))))+IF(C2<85,1,IF(AND(C2=85,C2<=94),2,IF(C294 ,3,"")))+IF(D2<48,1,IF(AND
(D2=48,D2<=53),2,IF(D253,3,"")))+IF(E2<19,3,IF(A ND(E2=19,E2<=21),2,IF
(E221,1,""))))/4

The problem I'm having is that if the value falls between the range with the
value of 1 for the first dataset I don't have a problem. If it falls within
the range with a value of 2 or 3 then it gives me a #VALUE! error. If I
delete all of the formula except for the criteria for the first dataset and
the data falls in the range of a value 2 or 3 then I get a blank cell that
states inconsistent formula.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200804/1

Often, Pivot Tables are like IF functions on steroids. Take a look at these
sites, and see if you can do what you want to do, but don't use an IF
function (use a Pivot Table to do it quicker and MUCH easier than the way you
are doing it now)
http://peltiertech.com/Excel/Pivots/pivottables.htm

http://www.contextures.com/xlPivot02.html


Regards,
Ryan---

--
RyGuy


"cabana_boy via OfficeKB.com" wrote:

I'm trying to write an if statment on 4 different pieces of data that gives
them a value of 1, 2, or 3 based on their values. The first set of data has a
range of -5,000,000 to 2,000,000, the second set of data ranges from 0 to 150,
the third set ranges from 0 to 140, the fourth set ranges from 0 to 130.

For the first set of data (b2), I need to assign a value of 1 for data
ranging from -821,524.12 to 866,503.73. I need to assign a value of 2 for
data ranging from -912,804.58 to -821,524.12 and 866,503.73 to 962,781.92. I
need to assign a value of 3 for values less than -912804.58 and greater than
962,781.92.

Ranges for the second set (c2) are 0 to 84 gets a value of 1, 85 to 94 gets a
value of 2, 95 and up gets a value of 3.

Ranges for the third set (d2) are 0 to 47 gets a value of 1, 48 to 53 gets a
value of 2, 54 and up gets a value of 3

Ranges for the fourth set (e2) are 0 to 18 gets a value of 3, 19 to 21 gets a
value of 2, 21 and up gets a value of 1 (note this is opposite from the
previous two data sets)

I'm taking the results and averaging them, so the formula I've come up with
is:

=(IF(AND(B2<866503.73,B2-821524.12),1,IF(AND(B2=866503.73,B2<=962781.92),2 ,
IF(AND(B2=-912804.58,B2<=-821524.12),2,IF(AND(B2<-912804.58,B2962781.92),3,
""))))+IF(C2<85,1,IF(AND(C2=85,C2<=94),2,IF(C294 ,3,"")))+IF(D2<48,1,IF(AND
(D2=48,D2<=53),2,IF(D253,3,"")))+IF(E2<19,3,IF(A ND(E2=19,E2<=21),2,IF
(E221,1,""))))/4

The problem I'm having is that if the value falls between the range with the
value of 1 for the first dataset I don't have a problem. If it falls within
the range with a value of 2 or 3 then it gives me a #VALUE! error. If I
delete all of the formula except for the criteria for the first dataset and
the data falls in the range of a value 2 or 3 then I get a blank cell that
states inconsistent formula.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200804/1

Each of the 4 parts of your formula can return empty string ("") which is
text, not zero. You then try to take the average by summing the 4 parts and
dividing by 4. This returns the #Value error since you can't add text to
numbers. Also, in the below snippet, note that C2 has already been tested for
if it is < 85. So it's logically not necessary to retest if it is =85. Same
applies to the 94. All 4 parts of your formula do similar.

Formula snippet:
IF(C2<85,1,IF(AND(C2=85,C2<=94),2,IF(C294,3,"") ))

Suggested formula:

=(IF(AND(B2-821524.12, B2<866503.73), 1, IF(AND(B2=-912804.58,
B2<=962781.92), 2, 3))+IF(C2<85, 1, IF(C2<=94, 2, 3))+IF(D2<48, 1, IF(D2<=53,
2, 3))+IF(E2<19, 3, IF(E2<=21, 2, 1)))/4

Greg

Works perfectly, thanks!!!

Greg Wilson wrote:
Each of the 4 parts of your formula can return empty string ("") which is
text, not zero. You then try to take the average by summing the 4 parts and
dividing by 4. This returns the #Value error since you can't add text to
numbers. Also, in the below snippet, note that C2 has already been tested for
if it is < 85. So it's logically not necessary to retest if it is =85. Same
applies to the 94. All 4 parts of your formula do similar.

Formula snippet:
IF(C2<85,1,IF(AND(C2=85,C2<=94),2,IF(C294,3,"" )))

Suggested formula:

=(IF(AND(B2-821524.12, B2<866503.73), 1, IF(AND(B2=-912804.58,
B2<=962781.92), 2, 3))+IF(C2<85, 1, IF(C2<=94, 2, 3))+IF(D2<48, 1, IF(D2<=53,
2, 3))+IF(E2<19, 3, IF(E2<=21, 2, 1)))/4

Greg

--
Message posted via http://www.officekb.com