I need some help with an Excel formula

[Stinky The Clown]

I need to enter a formula in an Excel worksheet that does the following:

Add a column of numbers. These numbers could total to a value of 140. I need the cell in which the total appears to calculate the total, but allow it to be the actual sum, but limit the maximum value to 60.

The number in the cell, whether it is less than or equal to 60, will then be added to other numbers elsewhere in the spreadsheet.

That one cell's formula is making me nuts. I don't know if its an if/then, a less than/equal to, or what.

If you can help, I'd **really** appreciate a syntactically correct and complete formula. I use Excel for only some very basic work, and this one just plain eludes me.

Thanks VERY much for any help anyone can lend me!!

[RoyGBiv]

1. Possible help ...

I don't have any spreadsheet program on my home computer and so can't offer a completely correct formula. I will guess at it, but I have to test these out within the program itself to make sure they work.

But, basically, if I'm reading this correctly, it's both an If/Then and a less than/equal too problem.

To clarify, you're wanting to add a column of numbers. If the sum of that column is =/< 60, you want to use that number, but if it is > 60, you want the sum to be 60. For example, if the sum is 59, the value would be 59. If the sum is 61, you want the value to be 60? Yes?

If so, this would be something like =IF(SUM(a1:a10)>60,60,SUM(a1:a10))

This probably isn't exactly right. The parentheses are probably off.

Your basic solution is: If the sum of cells a1 thru a10 is greater than 60, then the value equals 60, else the value equals the sum of cells a1 thru a10.

[welshTerrier2]

2. looks right to me ...

alternatively, you could try:

=IF(SUM(a1:a10)<60,SUM(a1:a10),60)

this says that if the total is less than 60, use the total, otherwise use exactly 60 ...

same result either way ...

[Stinky The Clown]

3. RoyGBiv and welshTerrier2, thanks VERY much!

The formula did **exactly** what I needed it to do.

I really, really appreciate your help.

I used this formula and only needed to substitute my own cell ranges.

=IF(SUM(a1:a10)<60,SUM(a1:a10),60)