If and Statement Problem

[sjuhl99]

I am trying to write the below formula using AF as my baseline referencing the buckets in AG thru AL as buckets identifying where AF falls in between and labeling however keep getting an error. Am i error out because of the last statement not being an "AND" statement and not closing the whole formula out right?

AF AG AH AI AJ AK AL
[TABLE="width: 474"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]Days Past due from Original Arrival[/TD]
[TD]Bucket[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]-10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 474"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=IF(AND(AF3>0,AF3<=AG1, "0-10",IF(AND(AF3>AG1,AF3<=AH1,"10-20",IF(AND(AF3>AH1,AF3<=AI1,"20-30",IF(AND(AF3>AI1,AF3<=AJ1,"30-40",IF(AND(AF3>AJ1,AF3<=AK1,"40-50",IF(AND(AF3>AK1,AF3<=AL1,"50-60",IF(AF3>AL1,"Greater than 60 days", "OK")))))))))))))

 

[jtakw]

Hi,

What is in AF that you're referencing in your formula?

 

[sjuhl99]

AF is the column with the header "Days Past due from Original Arrival", the value of -10 being in AF3. 10=AG1, 20=AH1....etc. I did revise it to close the and statements and lock the cell reference and still am getting a #VALUE ! error now.


=IF(AND(AF4>0,AF4<=$AG$1,"0-10"),IF(AND(AF4>$AG$1,AF4<=$AH$1,"10-20"),IF(AND(AF4>$AH$1,AF4<=$AI$1,"20-30"),IF(AND(AF4>$AI$1,AF4<=$AJ$1,"30-40"),IF(AND(AF4>$AJ$1,AF4<=$AK$1,"40-50"),IF(AND(AF4>$AK$1,AF4<=$AL$1,"50-60"),IF(AF4>$AL$1,"60+","OK")))))))

 

[JoshuaD]

You and statements have to have closed parenthesis. Without them the first and statement thinks all of the others are part of its own logic.


Try This
=IF(AND(AF3>0,AF3<=AG1),"0-10",IF(AND(AF3>AG1,AF3<=AH1),"10-20",IF(AND(AF3>AH1,AF3<=AI1),"20-30",IF(AND(AF3>AI1,AF3<=AJ1),"30-40",IF(AND(AF3>AJ1,AF3<=AK1),"40-50",IF(AND(AF3>AK1,AF3<=AL1),"50-60",IF(AF3>AL1,"Greater than 60 days", "OK")))))))

 

[Marcílio_Lobão]

The layout you typed in the message does not help at all to understand your need.

It looks like you are wanting to use a table and find what range the cell value in AF3 is in order to give it a pre-defined label.

Your formula should work that way:
=IF(AND(AF3>0,AF3<=AG1),"0-10",IF(AND(AF3>AG1,AF3<=AH1),"10-20", IF(AND(AF3>AH1,AF3<=AI1),"20-30",IF(AND(AF3>AI1,AF3<=AJ1),"30-40", IF(AND(AF3>AJ1,AF3<=AK1),"40-50", IF(AND(AF3>AK1,AF3<=AL1),"50-60", IF(AF3>AL1,"Greater than 60 days", "OK")))))))

But this case will be much better solved by using the specially created VLOOKUP function for that case.

I hope it helps.

 

[jtakw]

Hi,

How about a LOOKUP formula instead of a messy nested IFS:

Book1
	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1		10	20	30	40	50	60		OK
2									10-20
3	-10								40-50
4	12								Greater than 60 Days
5	45
6	65
Sheet5

Cell Formulas
Range	Formula
AN1	=IFERROR(LOOKUP(AF3,{0,11,21,31,41,51,61},{"0-10","10-20","20-30","30-40","40-50","50-60","Greater than 60 Days"}),"OK")

 

[sjuhl99]

The Lookup formula worked perfect, thank you very much and thank you everyone else that helped, really appreciate it! Quick question so I understand what the lookup is telling me, what is {0,11,21,31,41,51,61}, is that looking at AF3's value then bucketing it into {"0-10","10-20","20-30","30-40","40-50","50-60","Greater than 60 Days"}? I know vlookups but have never used just the lookup function.

 

[jtakw]

Hi,

You're very welcome. Yes, you're right, that's exactly what the formula is doing (BTW, now you don't need AG1:AL1 since the buckets are built in to the formula)

 

[jtakw]

One more suggestion, you might want to change "10-20" to "11-20", "20-30" to "21"30", etc. in the formula so that they don't overlap.

 

[sjuhl99]

excellent, will do and thanks again.