Forum Discussion
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.
9 Replies
- Every time I try to do an auto SUM or manual SUM the result is always ########. How can I fix this?
sabaine Please make this column wider, that's most probable reason
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)
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.
- Excellent Sergei, that works. Many thanks!
Graham370 , you are welcome
