Formula for calculating a payments

[Skylar2000]

Hi.

I need some help on a formula for calculating employee payments based on a bonus scheme. The calculation is probably rather simple, but I cannot get my head around it.

It is explained in the Excel attached. The fields I need to calculate is marked up in Yellow.

regards

Skylar2000

[Glenn Kennedy]

With a minor change to the layout:

=SUMPRODUCT((B2>$H$3:$H$5)*(B2-$H$3:$H$5)*$J$3:$J$5)

[Skylar2000]

That was fast Many thanks.

Two questions:
1. I see you have calculated differences. Why is 8% 0.03?
2. Can this be done by using IF and AND functions?

[Glenn Kennedy]

1. Because it at 0.08, it is 0.03 higher than the previous band, which was 0.05 - which in turn was 0.05 higher than the first band (0).

2. Probably, but it'll be much more complicated. and if you have to add another level or two... completely impossible. Far better to learn to use this way.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.