Solved

# Is there a way in Excel to do a formula to calculate a value per row but to limit to number to sum at a certain cutoff point?

Posted on 2013-05-17

I have a spreadsheet that I'm using to do so revenue projections and what I want to do is use one of the columns in a formula to calculate revenue based on a couple other variables.

The column has a range of values from 500 down to 0. What I want to do is to have another field be a variable so I can plug in a number (say 25) and only have the formula calculate a total up to 25 but ignore the remainder over 25.

Is there a function or a way to apply a type of "IF" statement to multiple rows?

The idea is "IF the number is greater than or equal to N use N, If less than N use the number in the column".

So for the below list the total would be: 25+25+25+22+13+3+0=113

464 (25)

268 (25)

101(25)

22 (22)

13 (13)

3 (3)

0 (0)

I want to just have a field I can type in a number and then have the totals adjust based on that variable number.

Is this possible? Thanks!

Ike