Democratic Underground Latest Greatest Lobby Journals Search Options Help Login
Google

I need some help with an Excel formula

Printer-friendly format Printer-friendly format
Printer-friendly format Email this thread to a friend
Printer-friendly format Bookmark this thread
This topic is archived.
Home » Discuss » DU Groups » Computers & Internet » Computer Help and Support Group Donate to DU
 
Stinky The Clown Donating Member (1000+ posts) Send PM | Profile | Ignore Sun Jun-05-05 05:34 PM
Original message
I need some help with an Excel formula
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!!
Printer Friendly | Permalink |  | Top
RoyGBiv Donating Member (1000+ posts) Send PM | Profile | Ignore Sun Jun-05-05 07:47 PM
Response to Original message
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.

Printer Friendly | Permalink |  | Top
 
welshTerrier2 Donating Member (1000+ posts) Send PM | Profile | Ignore Sun Jun-05-05 08:40 PM
Response to Reply #1
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 ...
Printer Friendly | Permalink |  | Top
 
Stinky The Clown Donating Member (1000+ posts) Send PM | Profile | Ignore Sun Jun-05-05 11:00 PM
Response to Original message
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)
Printer Friendly | Permalink |  | Top
 
DU AdBot (1000+ posts) Click to send private message to this author Click to view 
this author's profile Click to add 
this author to your buddy list Click to add 
this author to your Ignore list Fri Dec 27th 2024, 05:12 AM
Response to Original message
Advertisements [?]
 Top

Home » Discuss » DU Groups » Computers & Internet » Computer Help and Support Group Donate to DU

Powered by DCForum+ Version 1.1 Copyright 1997-2002 DCScripts.com
Software has been extensively modified by the DU administrators


Important Notices: By participating on this discussion board, visitors agree to abide by the rules outlined on our Rules page. Messages posted on the Democratic Underground Discussion Forums are the opinions of the individuals who post them, and do not necessarily represent the opinions of Democratic Underground, LLC.

Home  |  Discussion Forums  |  Journals |  Store  |  Donate

About DU  |  Contact Us  |  Privacy Policy

Got a message for Democratic Underground? Click here to send us a message.

© 2001 - 2011 Democratic Underground, LLC