need to solution on more nested IF (error getting this formula uses more levels of nesting)

[uzamr]

Hi all,

I'm having issues with the below-nested loop. (error getting this formula uses more levels of nesting)
this is matching like below

e.g: if the F4 value between 48 and 61 then 4000 need to print


=IF(AND(F4>=48,F4<61),4000,
IF(AND(F4>=61,F4<76),5000,
IF(AND(F4>=76,F4<91),6000,
IF(AND(F4>=91,F4<106),7000,
IF(AND(F4>=106,F4<121),8000,
IF(AND(F4>=121,F4<136),9000,
IF(AND(F4>=136,F4<151),10000,
IF(AND(F4>=151,F4<166),11000,
IF(AND(F4>=166,F4<181),12000,
IF(AND(F4>=181,F4<196),13000,
IF(AND(F4>=196,F4<211),14000,
IF(AND(F4>=211,F4<226),15000,
IF(AND(F4>=226,F4<241),16000,
IF(AND(F4>=241,F4<256),17000,
IF(AND(F4>=256,F4<271),18000,
IF(AND(F4>=271,F4<286),19000,
IF(AND(F4>=286,F4<301),20000,
IF(AND(F4>=301,F4<326),22000,
IF(AND(F4>=326,F4<351),24000,
IF(AND(F4>=351,F4<376),26000,
IF(AND(F4>=376,F4<401),28000,
IF(AND(F4>=401,F4<426),30000,
IF(AND(F4>=426,F4<451),32000,
IF(AND(F4>=451,F4<476),34000,
IF(AND(F4>=476,F4<501),36000,
IF(AND(F4>=501,F4<=525),38000,
"NO ADVANCE"))))))))))))))))))))))))))


Thanks.

 

[ER_Neha]

do you need excel formula or vba code will also be good to go?
secondly is there any other condition for your data , as i can see there is a initial difference of 13 then a series of 15 , then a series of 25 difference between upper and lower bound of f4

 

[ER_Neha]

Hello I Tried using your nested if, and it worked fine for me, I think you are inserting a enter after every IF statement, try not using any enter after every if statement. However providing your formula in a modified way. Kindly provide feedback.

Book1
	F	G
3
4	58	4000
Sheet1

Cell Formulas
Range	Formula
G4	G4	=IF(AND(F4>=48,F4<61),4000,IF(AND(F4>=61,F4<76),5000,IF(AND(F4>=76,F4<91),6000,IF(AND(F4>=91,F4<106),7000,IF(AND(F4>=106,F4<121),8000,IF(AND(F4>=121,F4<136),9000,IF(AND(F4>=136,F4<151),10000,IF(AND(F4>=151,F4<166),11000,IF(AND(F4>=166,F4<181),12000,IF(AND(F4>=181,F4<196),13000,IF(AND(F4>=196,F4<211),14000,IF(AND(F4>=211,F4<226),15000,IF(AND(F4>=226,F4<241),16000,IF(AND(F4>=241,F4<256),17000,IF(AND(F4>=256,F4<271),18000,IF(AND(F4>=271,F4<286),19000,IF(AND(F4>=286,F4<301),20000,IF(AND(F4>=301,F4<326),22000,IF(AND(F4>=326,F4<351),24000,IF(AND(F4>=351,F4<376),26000,IF(AND(F4>=376,F4<401),28000,IF(AND(F4>=401,F4<426),30000,IF(AND(F4>=426,F4<451),32000,IF(AND(F4>=451,F4<476),34000,IF(AND(F4>=476,F4<501),36000,IF(AND(F4>=501,F4<=525),38000,"NO ADVANCE"))))))))))))))))))))))))))

 

[Fluff]

The OP is asking how to add extra criteria, not complaining that it didn't work.

 

[Fluff]

How about

=IF(F4<48,"No Advance",IF(F4<301,(INT((F4-61)/15)+1)*1000+4000,IF(F4<526,(INT((F4-301)/25)+1)*2000+20000,"No Advance")))

 

[arthurbr]

Your existing formula can be reduced

 =IF(AND(F4>=48,F4<301),(CEILING(F4,15))/15*1000,IF(AND(F4>=301;F4<=525),20000+CEILING((F4-300),25)/25*2000,"no advance"))

More conditions can be added but we have to know the requirements

 

[ER_Neha]

How about

=IF(F4<48,"No Advance",IF(F4<301,(INT((F4-61)/15)+1)*1000+4000,IF(F4<526,(INT((F4-301)/25)+1)*2000+20000,"No Advance")))

Truly speaking, Sir, I was waiting for your reply, I knew there is a different way to solve this type of question, but as a beginner I don't know it. Thank you for reply, it helps me to gather knowledge too.