Multiple IF Statement not working.

**Stahlfeder** · 12-17-2012, 04:37 PM

Okay, so basically if C18 says Y, then it needs to multiply the order qty by .02 ($20 per 1000). it needs a minimum of $15, but after the first $20 it needs to round up to the nearest $20. So since the formula didn't like rounding up WITH the $15 minimum, I figured if I set it to round up to the nearest 20 ONLY if the order qty is 1000 or over, it would solve the problem, but now I"m getting a Value error, and I'm not sure how to fix it.
Can anyone help?

=IF(C18="Y",MAX(15,(PRODUCT(H6,0.02))),0),IF(H6=">1000",ROUNDUP(PRODUCT(H6,0.02)/20,0)*20)

Thanks

**Ace_XL** · 12-17-2012, 04:46 PM

Try this..

Please Login or Register  to view this content.

EDIT: edited formula above, should read as CEILING(H6*0.02,20) instead of the CEILING(H6*0.02,5)

**FDibbins** · 12-17-2012, 04:46 PM

your if() statement anctuall ends here...
=IF(C18="Y",MAX(15,(PRODUCT(H6,0.02))),0) - the rest is error
=IF(C18="Y",MAX(15,(PRODUCT(H6,0.02))),0) ,IF(H6=">1000",ROUNDUP(PRODUCT(H6,0.02)/20,0)*20)

not sure what you are trying to do, but maybe you need another if()?

and just out of curiosity, why are you using PRODUCT(H6,0.02), when (i think) you could just use H6*0.02)?

**Stahlfeder** · 12-17-2012, 04:48 PM

Almost, but it's not rounding up to the next $20 anymore.

**Stahlfeder** · 12-17-2012, 05:00 PM

Ace, it's perfect, thank you!

FDibbins, honestly? Excel isn't my forte. I'm just trying to put together what I thought was going to be a simple quote sheet for my boss, but it's proving to be more difficult than I anticipated. Everything I've learned I've figured out on my own.