Excel Formula help

864 Views 5 Replies 2 Participants Last post by etaf, Jan 11, 2016

benhur1978

Discussion Starter · Jan 9, 2016

Hi,
Thanks in advance for any help.

I trying to calculate a final commission value based on various parameters as follows...

If the sales value £1000 or less then the commission is 0%
From £1001 to £2999 the commission is 20%
From £3000 to £5999 the commission is 25%
Above £6000 the commission is 33%

I have worked out the following done in separate cells, but I'd prefer to be able to have it neat and tidy in 1 cell...

These are in cells B1 to B3
=IF(A1>6000,A1-6000,0)
=IF(A1>3000,A1-B1-2999,0)
=IF(A1>1000,A1-B1-B2-1000,0)

Then in column C I multiply each of the results of the above by the relevant %age and add them up.

As the final commission formula is used as part of a monthly summary report I'd rather not have calculate it in this seemingly cumbersome way.

Thanks again for any help.

Save

Status: Not open for further replies.

1 - 6 of 6 Posts

etaf

· #2 · Jan 9, 2016

you can use a lookup table and so if your commision changes all you need to do is update the table
OR
If the sales value £1000 or less then the commission is 0%
From £1001 to £2999 the commission is 20%
From £3000 to £5999 the commission is 25%
Above £6000 the commission is 33%

Do you just apply the commission as a straight value or in blocks

Assuming the value in A1
=IF(A1<=1000,0,IF(A1<3000,(A1-1000)*0.2,IF(A1<6000,(A1-3000)*0.25+(1999*0.2),IF(A1>=6000,(A1-6000)*0.33+(2999*0.25)+(1999*0.2),"incorrect Value"))))

I have used a nested IF

If the sales value £1000 or less then the commission is 0%
IF(A1<=1000,0,

From £1001 to £2999 the commission is 20%
IF(A1<3000,(A1-1000)*0.2,
As its less than 3000 , then we need to take 1000 away from whatever is in A1 and that gets 20%

IF(A1<6000,(A1-3000)*0.25+(1999*0.2),
Less than 6000
We get 1999 at 20%
and what ever is above 3000 at 25%

IF(A1>=6000,(A1-6000)*0.33+(2999*0.25)+(1999*0.2),
Greater or equal to 6000
we get 1999 at 20%
we get 2999 at 25%
and what ever is over 6000 at 33%

You may want to review the thresholds, and using the same formula you can change if you meant to use different numbers

See less See more

Save

benhur1978

Discussion Starter · #3 · Jan 10, 2016

Thanks for your reply Etaf, that looks great and the results are spot on.

Unfortunately the thresholds can't be changed for this one as they're contract in at those values.

Thanks again.

Save

etaf

· #4 · Jan 10, 2016 (Edited)

I was not thinking of the contract - but just making sure that the formula was correctly working with the thresholds that you needed for the sales
1000 = 0
2999 = 399.8
3000 = 399.8
as I have not included 3000 for the 0.25
so
=IF(A1<=1000,0,IF(A1<3000,(A1-1000)*0.2,IF(A1<6000,(A1-3000)*0.25+(1999*0.2),IF(A1>=6000,(A1-6000)*0.33+(2999*0.25)+(1999*0.2),"incorrect Value"))))
A1<3000 - does not include 3000
A1<6000,(A1-3000)*0.25 - this does and probably needs to be A1<6000,(A1-2999)*0.25

what do you pay for 3000 ?

its because 1000 is not included in your threshold - but 3000 and 6000 are

Just needs some of the values changed to give exactly the correct amount

what do you pay for
2999 = 399.8
3000 = 399.8
3001 = 400.5
5999 = 1149.55
6000 = 1149.55
6001 = 1149.88

=IF(A1<=1000,0,IF(A1<3000,(A1-1000)*0.2,IF(A1<6000,(A1-3000)*0.25+(2000*0.2),IF(A1>=6000,(A1-6000)*0.33+(3000*0.25)+(2000*0.2),"incorrect Value"))))

999 =0
1000 =0
1001 = 02

2999 = 399.8
3000 = 400
3001 = 400.25

5999 = 1149.75
6000 = 1150
6001 = 1150.33

See less See more

Save