IF function for multiple multiplications on one cell

[Natalieb95]

Hello,

In the attached Excel doc I have highlighted the cells in yellow that I am looking at. This is for working out commissions off of sales. The rule is:
If a sales consultant's total retail value is:
less than $200 = 0
$200-$300 = multiplied by 15%
$300-$400 = multiplied by 25%
$400 + = multiplied by 40%

I need the one cell (e.g AA8 to be able to have all those rules applied). Then I will be able to drag the formula down for the rest of the rows.

Thanks!

[alansidman]

=if(z8<200,0,if(and(z8>199,z8<300),z8*0.15,if(and(z8>299,z8<400),z8*0.25,z8*0.4)))

[Sam Capricci]

is this a tiered formula such as $375 sales, the first $200 gets nothing, the next $100 is multiplied by 15% then the last $75 is multiplied by 25%?
or is this a straight amount that all $375 would be multiplied by 25%?

if it is the 2nd one then this should work for you... =IF(Z8>199.99,Z8*0.15,IF(Z8>299.99,Z8*0.25,IF(Z8>399.99,Z8*0.4,0)))

[Sam Capricci]

disregard my if stmt, I had the values reversed (didn't test them )
should have been... =IF(Z8>399.99,Z8*0.4,IF(Z8>299.99,Z8*0.25,IF(Z8>199.99,Z8*0.15,0)))
an alternative would be this lookup... =Z8*(LOOKUP(Z8,{0,200,300,400},{0,0.15,0.25,0.4}))

[Natalieb95]

Hi Alan,

Thanks so much that's amazing.

If you want to go again I have another one on the new upload 'USE THIS ONE'

Highlighted in yellow again. This time the rules are:
1 sale = multiplied by 0
2 sales= multiplied by 0.5
3 sales= multiplied by 0.75
4 + = multiplied by 1

Looking at the values $20, $25, $30, $39, $49, $69 and if the consultant has done $99 and 3 sales I need it to multiply that by 0.75 because it's 3 sales.

[Sam Capricci]

here it is as a lookup... =O8*LOOKUP(N8,{1,2,3,4},{0,0.5,0.75,1})
here it is with an if statement... =IF(N8>3,O8*1,IF(N8>2,O8*0.75,IF(N8>1,O8*0.5,0)))

[alansidman]

=if(n8=1,0,if(n8=2,o8*0.5,if(n8=3,o8*0.75,o8)))

[GeoffW283]

And another option:
=O8*CHOOSE(MIN(N8, 4), 0,0.5,0.75,1)