Bonus Calculation with multiple targets

%3CLINGO-SUB%20id%3D%22lingo-sub-3206760%22%20slang%3D%22en-US%22%3EBonus%20Calculation%20with%20multiple%20targets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3206760%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20determine%20how%20I%20can%20calculate%20the%20amount%20of%20a%20bonus.%20In%20my%20example%20employees%20can%20get%20different%20bonus%20amounts%20based%20on%20how%20much%20they%20sell.%20There%20are%203%20thresholds%20and%20corresponding%20bonus%20amount.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E40-55%20apps%26nbsp%3B%20%26nbsp%3B%20%2425%20per%20app%20%26gt%3B%3D40%20and%20%26lt%3B%3D55%3C%2FP%3E%3CP%3E56-69%20apps%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2435mper%20app%20%26gt%3B%3D56%20and%20%26lt%3B%3D69%3C%2FP%3E%3CP%3E70%2B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2450%20per%20apps%20%26gt%3B%3D70%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20that%20will%20calculate%20how%20many%20apps%20fall%20within%20each%20threshold.%20For%20example%20if%20someone%20has%2057%20apps%20how%20many%2040-55%20and%20how%20many%2056-69%3F%20The%20payout%20would%20be%2016%20apps%26nbsp%3B%40%20%2425%20each%20and%202%20apps%26nbsp%3B%40%20%2435%20each.%20Total%20bonus%20would%20be%20%24470%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20that%20can%20do%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3206760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3206907%22%20slang%3D%22en-US%22%3ERe%3A%20Bonus%20Calculation%20with%20multiple%20targets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3206907%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316182%22%20target%3D%22_blank%22%3E%40khofreiter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3206959%22%20slang%3D%22en-US%22%3ERe%3A%20Bonus%20Calculation%20with%20multiple%20targets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3206959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316182%22%20target%3D%22_blank%22%3E%40khofreiter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20another%20route%2C%20just%20to%20illustrate%20the%20fact%20that%20in%20Excel%20there%20are%20often%20multiple%20ways%20to%20get%20from%20Point%20A%20to%20Point%20B.%3C%2FP%3E%3CP%3EEnter%20the%20number%20of%20apps%20in%20the%20yellow%20background%20cell%20and%20the%20bonus%20appears%20in%20the%20green.%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(B3%2CBonusTbl%2C3%2C1)%2B(((B3%2B1)-VLOOKUP(B3%2CBonusTbl%2C1%2C1))*(VLOOKUP(B3%2CBonusTbl%2C2%2C1)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1645658254179.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350817iFA38F1D0B76C7530%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1645658254179.png%22%20alt%3D%22mathetes_0-1645658254179.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, 

 

I am trying to determine how I can calculate the amount of a bonus. In my example employees can get different bonus amounts based on how much they sell. There are 3 thresholds and corresponding bonus amount. 

 

40-55 apps    $25 per app >=40 and <=55

56-69 apps     $35mper app >=56 and <=69

70+                 $50 per apps >=70

 

I am looking for a formula that will calculate how many apps fall within each threshold. For example if someone has 57 apps how many 40-55 and how many 56-69? The payout would be 16 apps @ $25 each and 2 apps @ $35 each. Total bonus would be $470

 

Is there a formula that can do this? 

 

Thank you for your help!

4 Replies

@khofreiter 

See the attached workbook.

@khofreiter 

 

Here's another route, just to illustrate the fact that in Excel there are often multiple ways to get from Point A to Point B.

Enter the number of apps in the yellow background cell and the bonus appears in the green.

This is the formula: 

=VLOOKUP(B3,BonusTbl,3,1)+(((B3+1)-VLOOKUP(B3,BonusTbl,1,1))*(VLOOKUP(B3,BonusTbl,2,1)))

 

mathetes_0-1645658254179.png

 

@khofreiter And another way to do it. See attached.

Screenshot 2022-02-24 at 06.38.49.png

 

Thank you everyone for your suggestions! This is very helpful. @Riny_van_Eekelen @mathetes @Hans Vogelaar