Trouble with formulas MIN/MAX within IF statements

%3CLINGO-SUB%20id%3D%22lingo-sub-1282200%22%20slang%3D%22en-US%22%3ETrouble%20with%20formulas%20MIN%2FMAX%20within%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282200%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20like%20the%20result%20to%20be%20either%20the%20sum%20of%20the%20parts%2C%20or%2C%20if%20the%20parts%20exceed%20100%2C%20100.%26nbsp%3B%20I've%20tried%20multiple%20variations%20of%20IF%20statements%20to%20get%20there%2C%20and%20tried%20including%20MIN%2FMAX%20within%20the%20IF%20statement%2C%20to%20no%20avail.%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2299.99%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3ECol%20A%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3ECol%20B%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3ECol%20C%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3ECATEGORY%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3EPERCENTAGE%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E24%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3ED%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E34%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E11%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.33%25%22%3ESUM%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3E102%3C%2FTD%3E%3CTD%20width%3D%2233.33%25%22%3EResult%20should%20be%20actual%20SUM%26nbsp%3Bif%20less%20than%20100%2C%20if%20more%20than%20100%2C%20100.%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1282200%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-1282211%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20formulas%20MIN%2FMAX%20within%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282211%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609744%22%20target%3D%22_blank%22%3E%40BDB1973%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMAX(100%2CSUM(B3%3AB8))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282216%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20formulas%20MIN%2FMAX%20within%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20misprint%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMIN(100%2CSUM(B3%3AB8))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282218%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20formulas%20MIN%2FMAX%20within%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282218%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3BOf%20course!!%20My%20fingers%20just%20didn't%20do%20what%20my%20mind%20was%20thinking%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282230%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20formulas%20MIN%2FMAX%20within%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282230%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20quick%20reply.%20In%20my%20actual%20file%2C%20the%20MIN%20statement%20provided%20would%20look%26nbsp%3Blike%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3DMIN(100%2CSUM(D3%2CD6%2CD9%2CD12%2CD15%2CD18)).%20But%20the%20result%20is%20still%20112%25%2C%20when%20I%20want%20to%20limit%20it%20to%20100%25.%26nbsp%3B%20If%20I%20use%20MAX%20instead%2C%20the%20result%20is%2010000%25%20Using%20MAX%20and%20adding%20%2F100%20of%20the%20formula%20gets%20the%20result%20desired%2C%20but%20don't%20believe%20that's%20the%20right%20way%20to%20go%20about%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282242%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20formulas%20MIN%2FMAX%20within%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282242%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3Band%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20slight%20change%20to%20the%20MIN%20statement%20provided%20solved%20the%20problem.%26nbsp%3B%20Instead%20of%20using%3CBR%20%2F%3E%26nbsp%3B%3DMIN(100%2CSUM(D3%2CD6%2CD9%2CD12%2CD15%2CD18))%2C%26nbsp%3B%20the%20cell%20range%20in%20my%20actual%20file%2C%20changing%20the%20100%20to%201%20did%20the%20trick.%20So%20the%20properly%20constructed%2C%20working%26nbsp%3Bformula%20is%20now%20%3DMIN(1%2CSUM(D3%2CD6%2CD9%2CD12%2CD15%2CD18))%2C%20which%20caps%20the%20percentage%20at%20100%25%20or%20returns%20the%20actual%20summed%20result%20that%20is%20less%20than%20100%25.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EMany%20thanks!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609744%22%20target%3D%22_blank%22%3E%40BDB1973%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282243%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20formulas%20MIN%2FMAX%20within%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282243%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609744%22%20target%3D%22_blank%22%3E%40BDB1973%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20values%20are%20in%20%25.%20Replace%20the%20100%20with%201.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'd like the result to be either the sum of the parts, or, if the parts exceed 100, 100.  I've tried multiple variations of IF statements to get there, and tried including MIN/MAX within the IF statement, to no avail.

Col ACol BCol C
CATEGORYPERCENTAGE 
A24 
B15 
C10 
D8 
E34 
F11 
SUM102Result should be actual SUM if less than 100, if more than 100, 100.

 

 

6 Replies
Highlighted

@BDB1973 Try this:

 

=MAX(100,SUM(B3:B8))

Highlighted

@Riny_van_Eekelen 

Perhaps misprint

=MIN(100,SUM(B3:B8))
Highlighted

@Sergei Baklan Of course!! My fingers just didn't do what my mind was thinking

Highlighted

Thanks for the quick reply. In my actual file, the MIN statement provided would look like this:

 =MIN(100,SUM(D3,D6,D9,D12,D15,D18)). But the result is still 112%, when I want to limit it to 100%.  If I use MAX instead, the result is 10000% Using MAX and adding /100 of the formula gets the result desired, but don't believe that's the right way to go about it.

Highlighted

@Sergei Baklan and @Riny_van_Eekelen 

 

A slight change to the MIN statement provided solved the problem.  Instead of using
 =MIN(100,SUM(D3,D6,D9,D12,D15,D18)),  the cell range in my actual file, changing the 100 to 1 did the trick. So the properly constructed, working formula is now =MIN(1,SUM(D3,D6,D9,D12,D15,D18)), which caps the percentage at 100% or returns the actual summed result that is less than 100%.


Many thanks!

@BDB1973 

Highlighted

@BDB1973 

Your values are in %. Replace the 100 with 1.