Is there an Excel formula to limit each number in a SUM formula to a certain value?
I've found how to create a cut-off for the total, but not each item.
An example of what I mean: I'm summing 6 numbers (in each row), but if a number is larger than 120 I want it to only be counted as 120 when being summed. (So I don't have to look over the 6#s to see if the total was inflated by 1 of the #s being VERY large, I'll know it's "balanced").
I figure I could put a MIN formula over each number, inside the sum (SUM(MIN(120,cell1),MIN(120,cell2)... etc), but if there's something simpler out there, I'd like to know (if only for future reference where I maybe dealing with more numbers per-sum).
- llafferLv 74 years agoFavorite Answer
I would create a few hidden columns that have IF statements checking the values of your fields. if it's more than 120, then result 120, otherwise the cell being checked:
B1 = IF(A1>120,120,A1)
then when you have your sums, sum up the hidden column.
then you can =SUM(B1..B100) (via a range, vs. adding up each individual cell), and if you need more, just make the range longer. And it's easy to copy the formulas from one cell to another.
- ?Lv 74 years ago