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
4
182 Views
Last Modified: 2014-03-18
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
Comment
Question by:Ike23
4 Comments
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 39176434
You could just use the MIN(field,field) function.

MIN(430;25) = 25
MIN(268;25) = 25
MIN(7;25) = 7
0
 
LVL 26

Expert Comment

by:redmondb
ID: 39176438
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
 
LVL 4

Author Comment

by:Ike23
ID: 39176443
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
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39176454
Instead of your IF, you could use:
=MIN(J19,$Q$7)*$O$3
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question