So I have this formula =IF(SUM(C3:L3)>=100,100,SUM(C3:L3)) and it sets L3 to 100 but when I try to have a cell subtract from L3 its not subtracting from the max number. For example C3=40, D3=70, E3=-20, it makes L3=90. Which is not right it should be 80. I hope this makes sense and someone can help. Cheers!
You get better help if you add an excel file, without confidential information.
Please also add the desired (expected) result in your sheet.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
It seems like you want to check for EVERY step of the sum (C3+D3, +E3, +F3 etc.) if the total reaches 100; this cannot be done with a simple formula... You'd need to make a formula for every step of your total sum...
When I say semicolon, u say comma!
=IF(SUM(C3:L3)>=100,100,SUM(C3:L3)) means that IF THE SUM FROM C3:L3 is greater than or equal to 100, enter 100. If that is FALSE then sum C3:L3.
The condition set with the IF statement "sum(C3:L3)>=100 is FALSE because with the values in C3, D3 and E3 being 40 70 -20 the sum is 90 therefore the 100 does not come into play as it is less than 100. The FALSE part of the statement takes over. SUM(C3:L3) and that is equal to 90.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks