Forum Discussion

Graham370's avatar
Graham370
Copper Contributor
Feb 01, 2021
Solved

SUM - set maximum value

I have created a formula and if the value of the calculation exceeds say 1,000 I want the value to default to 1,000.  Please advise how I can achieve this.
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 01, 2021

Graham370 

It could be

=MIN(1000, SUM(...) )
DavidSeton's avatar
DavidSeton
Copper Contributor
May 20, 2024

Hello, I'm waking this thread up because I am facing the same issue as the original poster, only the solution offered earlier doesn't seem to be working for me. What am I getting wrong? I am attaching a screenshot of my attempt at the formula according to the earlier solution (can't find how to attach the Excel file itself, not sure if I should be able to..?)

FYI cell E1 is the sum of A1:A8; B1; C1 - this is the total that I want to "limit" to 80000 in cell E2.

 

Thanks for any help and insight you can provide!

 

(Note: I am using Excel for Mac version 16.85)

 


SergeiBaklan 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 20, 2024

    DavidSeton 

    I guess you shall use semicolon in formula as separator, i.e. MIN(80000; SUM(A1:A8;B1;C1) )

    Use comma or semicolon depends on your regional settings, but it shall be the same everywhere.

    • DavidSeton's avatar
      DavidSeton
      Copper Contributor
      May 20, 2024
      Thanks a million, worked perfectly!

Resources