Index formula or match formula for calculation based on different tiers

[jsalotra]

Hi,

I am trying to come up with formula for calculation of bonus based on sales. The bonus rates are based on sales amount. Is there any formula other than if & then?

Please see the attached worksheet. Formula is required in column C so that bonus rate is populated based on bonus rates grid and sales amount in column B.

Please help.

Thanks!

[Sam Capricci]

without a bunch of if thens you could try this...
=LOOKUP(B12,{500000,750000,1000000,2000000,3000000},{0.015,0.02,0.03,0.04,0.05})
format then as percents.

[jtakw]

Hi,

Since you already have a Bonus Table, just change the structure of the Table to my sample below, then use:

Please Login or Register  to view this content.

Formula copied down.

Also, your description in Column E for the tiers are skipping 750K, 1M, 2M, & 3M, I've assumed you meant what I amended in Column E, also assumes any Sales below 500,000 is 0%.

See attached.

[avk]

Try in "A14"

Please Login or Register  to view this content.

copy paste across.

[jtakw]

Hi avk,

Are you sure you posted in the correct thread?

[avk]

yes jtakw, it is posted corrected.
Are you verify. can you explain.

[jtakw]

Your formula references doesn't match any of the cells in OP's uploaded file.

[avk]

Yes you are right jtakw. I posted in wrong post. sorry for inconvenience.

[jsalotra]

Thanks and is it possible to change the question or need to post again because I did not understand the contract for bonus properly and have to re-do all the calculations?

I would need a different formula for layers.
For example if the sales amount is $1,350,000, the bonus amount will be on first 750,000 - 1.5%, next 250,000 (upto 1M) - 2% and remaining 350,000 - 3%.
Please see the attached worksheet. as an example, I have sales amount for all sales person in Column B and bonus amount needs to be calculated in column C.
I have some manual calculations of bonus amount calculations but unable to come up with formula.

Thanks!

[jtakw]

Hi,

I posted a solution at your new thread here: https://www.excelforum.com/excel-gen...ml#post4961136