Return a number only if greater than another number? 1

[FYRGUY]

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

 

[ThePumpkin]

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

 

[KenWright]

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

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

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TonyJollans]

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

 

[FYRGUY]

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

Chris

 

[FYRGUY]

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

 

[SkipVought]

FYI,

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

Skip,
[sub]
[red][/red]
[/sub]

 

[FYRGUY]

Thanks Skip.

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

Chris

 

[SkipVought]

Check out Help...

Create or delete a custom number format

Skip,
[sub]
[red][/red]
[/sub]

 

[FYRGUY]

Thanks Skip.

Chris

 

[KenWright]

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..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TonyJollans]

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?

Enjoy,
Tony

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

Professional Office Developers Association

 

[KenWright]

LOL - No problem - just wanted to hammer home that message

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[FYRGUY]

Thanks to everyone.

Until next time...

Chris