help with an if statement

[cabana_boy via OfficeKB.com]

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,B2>962781.92),3,
""))))+IF(C2<85,1,IF(AND(C2>=85,C2<=94),2,IF(C2>94,3,"")))+IF(D2<48,1,IF(AND
(D2>=48,D2<=53),2,IF(D2>53,3,"")))+IF(E2<19,3,IF(AND(E2>=19,E2<=21),2,IF
(E2>21,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.

 

[ryguy7272]

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---

 

[Greg Wilson]

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(C2>94,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

 

[cabana_boy via OfficeKB.com]

Works perfectly, thanks!!!

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:

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