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