Bonus Structure Formula

[SheldonR]

Hi Everyone,

I hope you are all well & staying safe.

I've had a complete mind blank on creating the correct formula for a bonus structure & was wondering if you can help please?

I want the bonus amount to reflect what they hit. For example: -
They hit £8k and it works out the % bonus and it has the amount in the box.
When they then hit £15k it automatically jumps to the new %

I've attached the spreadsheet too.

Thanks in advance,

Sheldon.

[CARACALLA]

G3=IF($F3<>"",LOOKUP($F3,$E$19:$F$24,$G$19:$G$24),"")

Copy down

[SheldonR]

Thank you for such a quick reply!

[Richard Buttrey]

Hi,

Does this help. I assume it's a tiered bonus structure, which evaluates each band until the total is reached.

It uses the following formula in G3 copied down

Please Login or Register  to view this content.

Extend the table in E18:G24 with another column H for the difference in column G
i.e. H19 =G19
H20 =G20-G19 copied down to H24

See attached

[SheldonR]

Hi,

Does this help. I assume it's a tiered bonus structure, which evaluates each band until the total is reached.

It uses the following formula in G3 copied down

Please Login or Register  to view this content.

Extend the table in E18:G24 with another column H for the difference in column G
i.e. H19 =G19
H20 =G20-G19 copied down to H24

See attached

Thanks Richard, this works too. I appreciate your help on this, it has now been solved!