and statement in excel

May 19, 2010 at 01:44:47
Specs: Windows 7
 Can I ask for the correct syntax of this...=IF(AND(G35>=8000000,G10>=4000000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=7000000,G10>=3500000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=6000000,G10>=3000000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=5000000,G10>=2500000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=4000000,G10>=2000000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=3000000,G10>=1500000,((G35*0.025)*0.18)*0.4,0))))))

See More: and statement in excel

#1
May 19, 2010 at 04:04:38
 It appears that you failed to close off your =AND() functions:=IF(AND(G35>=8000000,G10>=4000000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=7000000,G10>=3500000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=6000000,G10>=3000000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=5000000,G10>=2500000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=4000000,G10>=2000000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=3000000,G10>=1500000),((G35*0.025)*0.18)*0.4,0))))))MIKE

Report •

#2
May 19, 2010 at 04:40:05
 Just out of curiosity...Is that your actual formula?Is "((G35*0.025)*0.18)*0.4" written as it is?Meaning, is "((G35*0.025)*0.18)*0.4" the actual numbers, or are they a formula, which you have just replaced with values for the sake of an example.(Hope I made myself clear.)If your formula is correct (as posted by Mike), then I see no reason why you can't shorten it to:=IF(OR(AND(G35>=8000000, G10>=4000000), AND(G35>=7000000, G10>=3500000), AND(G35>=6000000, G10>=3000000), AND(G35>=5000000, G10>=2500000), AND(G35>=4000000, G10>=2000000), AND(G35>=3000000, G10>=1500000)), G35*0.025*0.18*0.4, 0)It just seems to me that you do the exact same thing every time, regardless of the different sets of validations.I have tested both my and Mike's version.They seem to give the same results.*****************Edit:The formula is all on one line.

Report •

#3
May 19, 2010 at 05:03:01
 Wouldn't this work:=IF(AND(G35>=3000000,G10>=1500000),((G35*0.025)*0.18)*0.4,0)Since all the =AND() functions are of the Greater Than or Equal variety?MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
May 19, 2010 at 05:11:24
 Or even shorter....=IF(AND(G35>=3000000,G10>=1500000),G35*0.0018,0)And if G25 and G10 are whole numbers, we could go even shorter:=IF(AND(G35>2999999,G10>1499999),G35*0.0018,0)

Report •