With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Solved

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

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

4 Comments

Please see attached. The formula in C1 is...

```
=SUMIF(A1:A500,"<="&B1,A1:A500)+B1*COUNTIF(A1:A500,">"&B1)
```

Regards,Brian.Twenty-Five.xls

=IF(J19>$Q$7,($Q$7),(J19))

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!

Question has a verified solution.

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

Title | # Comments | Views | Activity |
---|---|---|---|

Text box keydown event does not work | 38 | 39 | |

Excel 2007 Change case in a url in a column | 3 | 26 | |

excel table with over 25000 rows. | 3 | 29 | |

vba copy paste previous line | 3 | 13 |

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