nested if statements - need help

[amario]

Not sure if anyone can help me with this

I have 2 sets of criteria.. and I'd like a formula that checks 2 cells
and then calculates a result based on the criteria of those 2 cells.
i'll try my best to explain.

Sales: $50,001 - $75,000
GP: $20,000-24,999

Cell B4 is where the sales number will be entered
Cell B5 will be where the GP is entered

What I want is to have a formula that says
If Sales are between 50,001-75,000 or if GP is btween 20k - 24,999,
then take 3% of GP.
or if Sales are 75k+ or GP is 25k+ then take 4% of GP.

Not sure if anyone can help me with this. I tried using various
versions of if statements, but it never works out.. THanks!

 

[joeu2004]

Sales: $50,001 - $75,000
GP: $20,000-24,999
Cell B4 is where the sales number will be entered
Cell B5 will be where the GP is entered
What I want is to have a formula that says
If Sales are between 50,001-75,000 or if GP is btween 20k - 24,999,
then take 3% of GP.
or if Sales are 75k+ or GP is 25k+ then take 4% of GP.

Your conditions are ambiguous. What do you want when
sales exceeds $75k, but GP is less than $25k? Your first
condition says 3%, but your second condition says 4%.
And what do you want when sales is $50k or less and/or
GP is less than $20k?

How about:

=if(or(b4>75000,b5>=20000), 4%*b5, 3%*b5)

Thus, when sales exceeds $75k or GP is $25k or more,
compute 4% of GP, otherwise 3% of GP. The latter case
covers when sales is $75k or less __and__ GP is less than
$25k.

 

[Bob Phillips]

=IF(OR(AND(Sales>50000.Sales<=75000),AND(GP>=20000,GP<25000)),GP*103%,IF(OR(
Sales>75000,GP>=25000),GP*104%,""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[joeu2004]

Typo ....

If Sales are between 50,001-75,000 or if GP is btween 20k - 24,999,
then take 3% of GP.
or if Sales are 75k+ or GP is 25k+ then take 4% of GP.

[....]
=if(or(b4>75000,b5>=20000), 4%*b5, 3%*b5)

That should be:

=if(or(b4>75000,b5>=25000), 4%*b5, 3%*b5)

 

[amario]

Thank you both your quick responses. Sorry for the confusion Joe, in the
case you specified where 75k is greater, I want it to be 4%.. the point
is basically set a comission percentage and multiply that by the gross
profit, according to the criteria i specified. So if sales are between
50k - 75k, but GP is greater than 25k, the commission should be 4%
because one of the criteria matches the higher leverl. If Sales are
above 75k, but GP is less than 20k, it should still give 4%. Does that
make sense? I'm sorry if it's still unclear, I'll try Bob's formula. If
you have any otehr suggestions, please let me know. thanks again for
your help guys.. ill respond if it doesn't workout

 

[joeu2004]

So if sales are between 50k - 75k, but GP is greater than
25k, the commission should be 4% because one of the
criteria matches the higher leverl. If Sales are above 75k,
but GP is less than 20k, it should still give 4%. Does that
make sense?

Sure! And if you closely at my IF(), I think you will see that
that is exactly how I interpreted your intention.

I'll try Bob's formula. If you have any otehr suggestions,
please let me know.

If you test Bob's formula carefully, I think you will see that it
produces 3%, not 4%, for the first case above (sales between
50-75K and gp greater than 25k). Not Bob's fault: he simply
implemented what you wrote the first time. GIGO.

 

[amario]

Oh i see. Ok.. Thanks Joe.. i'll give it a shot.. appreciate the help

 

[amario]

Hi Joe,

I tried your formula, but it didn't work. It's missing the fact that if
the sales are below 50k and GP is below 20k, there should be zero
commission, it should not allow 3 or 4% commission. Any suggestions?

- Amar

 

[amario]

Ok, Bob's formula worked perfectly. Thanks everyone!

- Amar

 

[comish4lif]

Zero commission... now was that in the original question?

"But you weren't being very specific, now, were you, Bob?" - Phenomenon