Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

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?

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
0
Ike23
Asked:
Ike23
1 Solution
 
HugoHiaslCommented:
You could just use the MIN(field,field) function.

MIN(430;25) = 25
MIN(268;25) = 25
MIN(7;25) = 7
0
 
redmondbCommented:
Hi, Ike23.

Please see attached. The formula in C1 is...
=SUMIF(A1:A500,"<="&B1,A1:A500)+B1*COUNTIF(A1:A500,">"&B1)

Open in new window

Regards,
Brian.Twenty-Five.xls

Edit: Apologies - silly mistake corrected.
0
 
Ike23Author Commented:
I figured out how to do the IF statement and use the $ to keep the variable column constant.

=IF(J19>$Q$7,($Q$7),(J19))*$O$3

Basically this says if the column is greater than my variable limit use the variable number, if not use the column number and then multiply it times the amount in my other variable column.

Works great!
0
 
byundtCommented:
Instead of your IF, you could use:
=MIN(J19,$Q$7)*$O$3
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now