IF forumla

[timcbaker]

Hi,

I have the current formula in cell M14:
=-IF(SUM($M$8>B14),SUM($M$8-B14),0)

However, I only want it to work if there is a value in cell B14

If cell B14 is blank I want cell M14 to show 00:00 (Its a time formula).

Can anyone help?

Thanks,
Tim

 

[Gerald Higgins]

Hi, welcome to the board.

First, you can simplify your existing formula quite alot, like this

=IF($M$8>B14,$M$8-B14,0)

And perhaps even further, like this

=MAX($M$8-B14,0)

If you want to force a 0 in M14 if B14 is zero, perhaps

=IF(B14=0,0,MAX($M$8-B14,0))

 

[jim may]

Something like:

=IF(ISNUMBER(B14),-M8+B14,"") or change "" to 0 (zero)

Will test B14 to insure it is a valid number (positive or negative)..

Format Cell M14 as time (00:00)

Hope that helps

 

[timcbaker]

That's great - thanks guys !

Okay, next one..

=-IF(SUM($O$8>E15),SUM($O$8-E15),0)

I only want this calculating if there is an entry in cell B15

 

[Joe4]

You are not using the SUM function correctly.
You use it when summing up a whole range of cells, i.e.

=SUM(A1:A10)

It literally says "add up all the cells in the range).

So you would not use it on a single cell, or wrap other formula on single cells.
You can essentially remove them from all the formulas you have posted in this thread.

Okay, next one..

=-IF(SUM($O$8>E15),SUM($O$8-E15),0)

I only want this calculating if there is an entry in cell B15

Try:

=IF(B15<>"",MAX($O$8-E15,0),"")

Based on your two questions, hopefully, you can see the pattern here now.

 

[snjpverma]

=-IF(b15,Max($O$8-E15,0),0)

 

[timcbaker]

Thanks
what if I want to amend this slightly.
If there is value in cell G15 and/or H15 I don't want the formula to calculate and just show 00:00 ?

 

[Joe4]

If there is value in cell G15 and/or H15

There are AND and OR functions in Excel.
But you need to decide which one you need to use.
With AND, all individual conditions must be TRUE to return TRUE.
With OR, only one condition must be TRUE to return TRUE.

See: https://www.ablebits.com/office-addins-blog/2014/12/17/excel-and-or-xor-not-functions/

 

[timcbaker]

I think 'or' would work on this occasion

 

[Joe4]

I think 'or' would work on this occasion

OK. Take a crack at it and see how you do, and post back if you run into issues.
That way we can see if we are really helping you understand and learning how to do these (instead of simply doing them all for you).