Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

IF(B4-40<0,0,(B4-40))

Above is my formula to show only positive numbers above 40 in the given cell. If my hours are ...lets say 35, I do not want a value of -5, I want the cell value to be 0. Why is this not working?

share|improve this question

closed as not a real question by Breakthrough, Simon Sheehan, 8088, Ƭᴇcʜιᴇ007, terdon Dec 11 '12 at 3:10

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
What is it displaying with that formula? It that works for me... –  nerdwaller Dec 10 '12 at 20:13
    
Nothing, it shows the formula.... my original cell formula was "=SUM(B4-40)". However, in an instance where the value of B4 equals "35", I was getting a "-5" value, and I do not want any negative numbers. The senario is, hours worked per week, and this cell is overtime hours. Or in other words, how many hours over 40. –  Jeff Holbrooks Dec 10 '12 at 20:19
    
@JeffHolbrooks was, or am? I just tried the formula on two entirely different programs under different operating systems, and got the expected result. Make sure your input data is well formed, and you haven't changed any of the formula indices. –  Breakthrough Dec 10 '12 at 20:24
    
Works for me as well, odd. imgur –  nerdwaller Dec 10 '12 at 20:30

4 Answers 4

Above is my formula to show only positive numbers above 40 in the given cell. If my hours are ...lets say 35, I do not want a value of -5, I want the cell value to be 0. Why is this not working?

I see no reason for that formula to work. Ensure the value stored in B4 is an integer and not a string. It's possible the order of operations is being mis-interpreted: as written, IF(B4-40<0,0,(B4-40)) may be checking if -40 is less than zero; that being said, I doubt this is the case, as the formula should evaluate the arithmetic operation before the comparison. Try adding additional brackets around the B4-40 term (for conciseness more than anything):

=IF( (B4-40) < 0, 0, (B4-40) )

I've verified that both the above formula, as well as the original, do indeed work properly on both MS Excel 2007 under Windows, as well as Gnumeric under Linux.

share|improve this answer
    
HERE IS THE EXACT FORMULA CUT AND PASTED --> IF((B3-40)<0,0,(B4-40)) NO SPACES, AND B4 IS DEFINATIVELY AN INTEGER. ITS THE ONLY VALUE MANUALLY INPUTTED INTO THE SPREADSHEET. IT STILL JUST SHOWS THE FORMULA. –  Jeff Holbrooks Dec 10 '12 at 20:29
4  
No need to shout... If it's exactly like that you are simply missing the = in front of the formula (see my answer) –  Matteo Dec 10 '12 at 20:35

You are missing an = in front of the formula. Without it Excel will interpret it as text.

=IF((B4-40)<0,0,(B4-40))
share|improve this answer
1  
I was trying to get at this in the comments before suggesting it, I was thrown off by 'it shows nothing'. +1 –  nerdwaller Dec 10 '12 at 20:37
    
@nerdwaller I noticed it after the comment with "Exact formula" ... –  Matteo Dec 10 '12 at 20:37
    
@Matteo - Your "B3" should be "B4", or at least they should be the same. I tried to edit this, but wasn't allowed to make such a "trivial" edit... wanted me to add more characters. –  Kevin Fegan Dec 10 '12 at 21:19
    
@KevinFegan Ooops, corrected. Thanks –  Matteo Dec 11 '12 at 6:24

Your original formula as you have shown it is missing the "=" (equals) sign at the beginning of the formula. Without the "=" sign, the formula will be shown as text.

I also don't see a reason why that formula isn't working for you, assuming you do have the "=" sign at the beginning of the formula. I would have written it a little differently:

=IF(B4>40,(B4-40),0)
or
=IF((B4>40),(B4-40),0)

You can test if B4 is actually a number (rather than a string) like this:

=IF(ISNUMBER(B4),IF((B4>40),(B4-40),0),"B4 is Not a number")

Please note that if you are using Open-Office Calc rather than Microsoft Excel, you will need to use semi-colons (;) instead of commas (,) like this:

=IF((B4>40);(B4-40);0)
=IF(ISNUMBER(B4);IF((B4>40);(B4-40);0);"Not a number")
share|improve this answer

It works for me, I get 0 for values less than 35 or 35, what version of excel you are using? You can also check type of each of your cell that could cause potential issues.

share|improve this answer
    
THANK YOU FOR YOUR HELP...I FEEL SO STUPID FOR THIS ERONEOUS MISTAKE.... IT SHOULD BE B3..... NOT B2. B2 IS PART OF MY HEADER WITH AN ALPHA NUMERIC STRING. STUPID LITTLE MISTAKE. IT WORKS NOW –  Jeff Holbrooks Dec 10 '12 at 20:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.