Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 total points
ID: 39176454
Instead of your IF, you could use:
=MIN(J19,$Q$7)*$O$3
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

670 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