Home

Need Help Writing a Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-752944%22%20slang%3D%22en-US%22%3ENeed%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752944%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20writing%20a%20formula%20based%20on%20this%20written%20out%20process%20to%20figure%20my%20staff's%20monthly%20bonuses.%26nbsp%3B%20I%20am%20not%20sure%20whether%20I%20should%20use%26nbsp%3B%20IF%2C%20IFS%2C%20or%20VLookUp%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3A%20Is%20Current%20Month%20%26gt%3B%3D%20Prior%20Year's%20Month%20by%208%25%3F%20No%2C%20then%20move%20to%20Step%202.%26nbsp%3B%20Yes%2C%20then%20multiply%20Current%20Month%20by%204%25.%3C%2FP%3E%3CP%3EStep%202%3A%20Is%20Current%20Month%20%26gt%3B%3D%20Prior%20Year's%20Month%20by%206%25%3F%20No%2C%20then%20move%20to%20Step%203.%26nbsp%3B%20Yes%2C%20then%20multiply%20Current%20Month%20by%203%25.%3C%2FP%3E%3CP%3EStep%203%3A%20Is%20Current%20Month%20%26gt%3B%3D%20Prior%20Year's%20Month%20by%204%25%3F%26nbsp%3B%20No%2C%20then%20move%20to%20Step%204.%26nbsp%3B%20Yes%2C%20then%20multiply%20Current%20Month%20by%202%25.%3C%2FP%3E%3CP%3EStep%204%3A%26nbsp%3B%20Is%20Current%20Month%20%26gt%3B%3D%20Prior%20Year's%20Month%20by%202%25%3F%26nbsp%3B%20No%2C%20then%20No%20Bonus.%26nbsp%3B%20Yes%2C%20then%20multiply%20Current%20Month%20by%201%25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-752944%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-752973%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375641%22%20target%3D%22_blank%22%3E%40dav7922%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20in%20A1%20is%20current%20to%20prev%20month%20%25%2C%20when%20bonus%20%25%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DMAX(MIN(INT(A1%2F0.02)%2F100%2C0.08)%2C0)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753284%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375641%22%20target%3D%22_blank%22%3E%40dav7922%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Current%2C%20Prior%2C%20and%20Change%20are%20in%20A2%2C%20B2%2C%20and%20C2%2C%20respectively%2C%20this%20formula%20in%20D2%20returns%20your%20desired%20result%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D(C2%26gt%3B%3D0.02)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EMIN(FLOOR(C2%2F2%2C0.01)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E0.04)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EUsing%20the%20formula%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20the%20formula%20in%20D2%20would%20be%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMAX(MIN(INT(C2%2F0.02)%2F100%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3E0.04%3C%2FSTRONG%3E)%2C%3C%2FP%3E%3CP%3E0)%3C%2FP%3E%3CP%3ENote%20that%20the%20number2%20argument%20of%20MIN%20should%20have%20been%20%3CSTRONG%3E0.04%3C%2FSTRONG%3Einstead%20of%20%3CSTRONG%3E0.08%3C%2FSTRONG%3E%2C%20which%20I%20guess%20was%20a%20typographical%20error.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753363%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20my%20negligence%2C%20thank%20you%20for%20the%20correction%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753365%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753365%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753723%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375641%22%20target%3D%22_blank%22%3E%40dav7922%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGentlemen%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20two%20formulas.%26nbsp%3B%20I%20tried%20them%20both%20and%20they%20both%20returned%20the%20same%20number%20but%20it%20is%20not%20what%20I%20am%20looking%20for.%26nbsp%3B%20Here%20is%20what%20I%20did%20based%20on%20what%20I%20understood%20from%20your%20responses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20Year%20A2%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Previous%20Year%20B2%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Difference%20C2%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Formula%20D2%3C%2FP%3E%3CP%3E1000%20%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%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20800%20%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%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20200%20%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%20%26nbsp%3B%26nbsp%3B%200.04%3C%2FP%3E%3CP%3E1000%20%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%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20800%20%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%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20200%20%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%20%26nbsp%3B%20%26nbsp%3B%200.04%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753777%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375641%22%20target%3D%22_blank%22%3E%40dav7922%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20suggest%20using%20a%20VLOOKUP%20formula%20with%20bracket%20lookup.%20You%20need%20to%20build%20a%20lookup%20table%20with%20brackets%20for%20different%20values%20of%20the%20ratio%20between%20current%20month%20and%20prior%20year%20same%20month%20sales.%20I%20put%20mine%20in%20cells%20Q2%3AR6%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20323px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123245iCDF5616361AACD44%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20A1%20is%20current%20month%20sales%20and%20A2%20is%20prior%20year%20same%20month%20sales%2C%20the%20bonus%20may%20be%20found%20as%3A%3C%2FP%3E%0A%3CPRE%3E%3DVLOOKUP(A1%2FA2%2C%24Q%242%3A%24R%246%2C2)*A1%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753875%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Writing%20a%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375641%22%20target%3D%22_blank%22%3E%40dav7922%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20shall%20multiply%20received%20%25%20in%20D2%20on%20current%20sales%20in%20A2%20to%20receive%20the%20bonus%20to%20pay%20if%20that's%20how%20it%20is%20calculated.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBoth%20formulas%20return%20per%20cent%20as%20it%20was%20asked%20(4%25%2C%20or%200.04%20in%20your%20sample).%3C%2FP%3E%3C%2FLINGO-BODY%3E
dav7922
New Contributor

I need help writing a formula based on this written out process to figure my staff's monthly bonuses.  I am not sure whether I should use  IF, IFS, or VLookUp formulas.

 

Step 1: Is Current Month >= Prior Year's Month by 8%? No, then move to Step 2.  Yes, then multiply Current Month by 4%.

Step 2: Is Current Month >= Prior Year's Month by 6%? No, then move to Step 3.  Yes, then multiply Current Month by 3%.

Step 3: Is Current Month >= Prior Year's Month by 4%?  No, then move to Step 4.  Yes, then multiply Current Month by 2%.

Step 4:  Is Current Month >= Prior Year's Month by 2%?  No, then No Bonus.  Yes, then multiply Current Month by 1%.

 

Thank you for the help.

 

7 Replies

@dav7922 

If in A1 is current to prev month %, when bonus % could be

=MAX(MIN(INT(A1/0.02)/100,0.08),0)

 

 

 

@dav7922 

If Current, Prior, and Change are in A2, B2, and C2, respectively, this formula in D2 returns your desired result: 

=(C2>=0.02)*

MIN(FLOOR(C2/2,0.01),

0.04)

Using the formula of @Sergei Baklan , the formula in D2 would be like this: 

=MAX(MIN(INT(C2/0.02)/100,

0.04),

0)

Note that the number2 argument of MIN should have been 0.04 instead of 0.08, which I guess was a typographical error. 

@Twifoo , my negligence, thank you for the correction

You’re welcome, @Sergei Baklan.

@dav7922 

 

Gentlemen,

 

Thank you for the two formulas.  I tried them both and they both returned the same number but it is not what I am looking for.  Here is what I did based on what I understood from your responses.

 

Current Year A2        Previous Year B2      Difference C2      Formula D2

1000                          800                           200                      0.04

1000                           800                          200                       0.04

@dav7922 

I suggest using a VLOOKUP formula with bracket lookup. You need to build a lookup table with brackets for different values of the ratio between current month and prior year same month sales. I put mine in cells Q2:R6

image.png

If A1 is current month sales and A2 is prior year same month sales, the bonus may be found as:

=VLOOKUP(A1/A2,$Q$2:$R$6,2)*A1

@dav7922 

 

You shall multiply received % in D2 on current sales in A2 to receive the bonus to pay if that's how it is calculated.

 

Both formulas return per cent as it was asked (4%, or 0.04 in your sample).

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies