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

Report •

#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

http://www.skeptic.com/


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?

MIKE

http://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 •

Ask Question