Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Return a number only if greater than another number? 1

Status
Not open for further replies.

FYRGUY

Technical User
Nov 27, 2005
42
US
Greetings,

In MS Excel, in a single worksheet, I am trying to sum cells F17 & O21 and put the answer in cell O22. I have a certian critera that follows. I have tried =IF and =SUMIF and can't seem to figure it out. Probably simple but I am out of attempts. Here is what I am trying to do spelled out, followed by my attempts at a function

If F17+O21 are >216, display the number over "216" in O22. If F17+O21 are <=216, leave O22 blank.

=IF((F17+O21)>216,(F17+O21)-216,(F17+O21))
+SUMIF(F17+O21,>216,F17+O21)

I hope my problem is glaring, but I can't find it. Please help if you can.

Chris
 
Hi Chris

You're problem appears to be in the FALSE criteria which is why it's not working properly. Try this instead:

=IF(F17+O21>216,(F17+O21)-216," ")

The FALSE criteria of " " means you'll get a blank cell, if you leave this criteria out the word FALSE will appear.

Hope this helps.

Cheers
Karen
 
Don't need the second set of parentheses, or the space either:-

=IF(F17+O21>216,F17+O21-216,"")

Regards
Ken............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
You could also achieve it with Formatting.

Just put the simple formula in the cell:
[tt] =F17+O21-216[/tt]
And format it as:
[tt] General;;[/tt]

This will leave the value of the calculation in the cell - and just not display it unless positive - so that it can be used in further calculations if you want.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association
 
Sorry it took so long for my reply back to everyone. I will give these suggestions a try. Thank you very much

Chris
 
Tony,
The "General;;" worked perfect, Thank you.

Could you briefly explain the ";;" behind General. I wasn't even aware of something like that. I assume there are other special symbols that do things. Is there a good book or a place I could reference these.

Thanks again

Chris
 



FYI,

The FALSE return value ought NOT to be a space, Karen.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Thanks Skip.

Can you shed any light on the ";;" after General in my last post?

Chris
 



Check out Help...

Create or delete a custom number format


Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
FYR Guy, be aware though, that by using a custom number format, what you are doing is FORMATTING only, and whilst this may be perfectly fine for you, just because you can't see the numbers, doesn't mean they aren't there, so you couldn't for example sum the entire set of data that you can see, and get just the values that you can see. (At least not without building the same criteria into your SUM formula)

Regards
Ken..........

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Hi Chris,

The semicolons are not special formatting characters they are delimiters between different sets of formatting.

1. Format for positive numbers - General in this case
2. Semicolon delimiter
3. Format for negative numbers - blank in this case
4. Semicolon delimiter
3. Format for zero - blank in this case
6. Further semicolon delimiter omitted in this case
7. Format for non-numeric data - defaults to General

Ken - I did say that up front - I suspect it may not matter in this case but each method has its own uses. But what do I know, I'm a Word MVP? [smile]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association
 
LOL - No problem - just wanted to hammer home that message :)

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Thanks to everyone.

Until next time...

Chris
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top