SOLVED

IFS Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2846206%22%20slang%3D%22en-US%22%3EIFS%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2846206%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20your%20help!%20I'm%20not%20at%20all%20familiar%20with%20the%20%22IF%22%20and%20%22IFS%22%20formulas.%20I%20actually%20got%20it%20to%20work%20on%20one%20part%20of%20my%20document%2C%20but%20then%20I%20wanted%20to%20add%20another%20argument%20to%20the%20formula%2C%20and%20I%20can't%20figure%20out%20how%20to%20write%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20first%20argument%20is%20that%20we%20will%20only%20reimburse%20an%20employee%20a%20maximum%20of%20%24150%20(cell%20I%20is%20the%20maximum%20alloted)%20for%20the%20purchase%20of%20security%20boots.%20Anything%20under%20%24150%20is%20paid%20in%20full%20(cell%20H).%20Up%20to%20here%20all%20works%20fine.%20My%20second%20argument%20is%3A%20if%20there%20is%20a%20%22s%22%20in%20cell%20D%2C%20then%20the%20maximum%20paid%20is%20%24230%2C%20but%20if%20the%20employee%20purchases%20boots%20valued%20at%20less%20than%20%24230%20but%20he%20has%20a%20%22s%22%20in%20cell%20D%2C%20then%20we%20reimburse%20the%20amount%20he%20paid%20(cell%20H)%20(as%20long%20as%20it%20doesn't%20go%20over%20the%20%24230).%26nbsp%3B%20So%20if%20you%20look%20at%20the%20last%20line%20of%20the%20table%2C%20the%20employee%20paid%20%24275.94.%20Since%20he%20has%20a%20%22s%22%2C%20the%20maximum%20in%20cell%20I%20should%20be%20topped%20at%20%24230%2C%20not%20%24150).%20The%20formula%20that%20I%20tried%20to%20use%20in%20cell%20I15%20to%20change%20this%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3ESI.CONDITIONS(D15%3D%22S%22%3B230%3BD15%3D%22%22%3B150%3BH15%26gt%3B150%3B150)%20where%20si.conditions%20%3D%20IFS%20in%20English.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20PLEASE%20help%20me%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20and%20my%20software%20is%20in%20French%2C%20so%20sorry%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2846206%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-2846414%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2846414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1185586%22%20target%3D%22_blank%22%3E%40MelanieU%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMIN(H15%3BSI(D15%3D%22s%22%3B230%3B150))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESI(D15%3D%22s%22%3B230%3B150)%20returns%20230%20if%20D15%20equals%20%22s%22%2C%20150%20otherwise.%3C%2FP%3E%0A%3CP%3EMIN(H15%3BSI(D15%3D%22s%22%3B230%3B150))%20returns%20either%20the%20Montant%20pay%C3%A9%20or%20230%2F150%2C%20whichever%20is%20lower.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2846424%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2846424%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!%20Works%20like%20a%20charm!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need your help! I'm not at all familiar with the "IF" and "IFS" formulas. I actually got it to work on one part of my document, but then I wanted to add another argument to the formula, and I can't figure out how to write it. 

 

My first argument is that we will only reimburse an employee a maximum of $150 (cell I is the maximum alloted) for the purchase of security boots. Anything under $150 is paid in full (cell H). Up to here all works fine. My second argument is: if there is a "s" in cell D, then the maximum paid is $230, but if the employee purchases boots valued at less than $230 but he has a "s" in cell D, then we reimburse the amount he paid (cell H) (as long as it doesn't go over the $230).  So if you look at the last line of the table, the employee paid $275.94. Since he has a "s", the maximum in cell I should be topped at $230, not $150). The formula that I tried to use in cell I15 to change this is: 

SI.CONDITIONS(D15="S";230;D15="";150;H15>150;150) where si.conditions = IFS in English.

 

Can you PLEASE help me??

 

Thanks in advance  

 

Oh and my software is in French, so sorry in advance!

Capture d’écran 2021-10-14 095219.png

2 Replies
best response confirmed by MelanieU (New Contributor)
Solution

@MelanieU 

You can use

 

=MIN(H15;SI(D15="s";230;150))

 

SI(D15="s";230;150) returns 230 if D15 equals "s", 150 otherwise.

MIN(H15;SI(D15="s";230;150)) returns either the Montant payé or 230/150, whichever is lower.

@Hans Vogelaar Thank you so much! Works like a charm!!!