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

 

[timcbaker]

I can't get it to work. I keep getting errors

As a reminder this formula:
=-IF(B14+G14+H14,MAX($O$8-E14,0),0)

I don't want it to work if there is a entry in cell G14 or H14

Thanks again for all your assistance

 

[Gerald Higgins]

I keep getting errors

Really ? What kind of errors ?

As a reminder this formula:
=-IF(B14+G14+H14,MAX($O$8-E14,0),0)

What do you mean, "a reminder" ?
I think that's the first time that formula has appeared in this thread.

I don't want it to work if there is a entry in cell G14 or H14

Really ? You DON'T want it to WORK ?

I think it would help if you slow down a bit and describe exactly what you want this formula to do.
That way we might be able to help you better.

 

[Joe4]

You didn't incorporate the OR function. The link I provided shows you examples of how to do that.
I am not sure what you are trying to do with B14+G14+H14.

Maybe this is what you want:

=IF([COLOR=#ff0000]OR(H14<>"",G14<>"")[/COLOR],0,[COLOR=#333333]MAX($O$8-E14,0))[/COLOR]

 

[timcbaker]

Apologise, I am getting confused by it all!

This formula:
=-IF(B15,MAX($O$8-E15,0),0)
that someone suggested earlier in thread (an adaptation of my original formula) works fine.

However, if there are entries in cells G14+H14 I just want the result to be 00:00

I tried adding OR function but I couldn't get it to work

 

[Joe4]

Just wrap that other formula in a new nested IF.

So here is the formula you currently have:

[COLOR=#333333]=-IF(B15,MAX($O$8-E15,0),0)[/COLOR]

You new condition would look like this:

=IF(OR(G14<>"",H14<>""),0,[I]original formula[/I])

so just drop the original in (without the equal sign), i.e.

[COLOR=#ff0000]=IF(OR(G14<>"",H14<>""),0,[/COLOR][COLOR=#0000ff]-IF(B15,MAX($O$8-E15,0),0)[/COLOR][COLOR=#ff0000])[/COLOR]

By the way, it looks rather odd to me that you have a negative sign in from of your first formula, which returns the negative of the result.
If you really intended to do that, just simply reverse the order of your subtraction and drop the negative, i.e.

[COLOR=#0000FF]=IF(B15,MAX(E15-$O$8,0),0)[/COLOR]

 

[timcbaker]

thanks Joe-that's the magic formula I needed.

If I wanted to get some training to be an advanced excel user, can you recommend any online courses or books?

 

[Joe4]

If I wanted to get some training to be an advanced excel user, can you recommend any online courses or books?

hiker95 has put together a rather extensive list of resources.
See here: https://www.mrexcel.com/forum/lounge-v-2-0/1031751-two-novice-questions-post4952119.html#post4952119

 

[timcbaker]

hiker95 has put together a rather extensive list of resources.
See here: https://www.mrexcel.com/forum/lounge-v-2-0/1031751-two-novice-questions-post4952119.html#post4952119

thanks !

 

[Joe4]

You are welcome. A lot of those links he posted have to do with VBA, but not all of them.
Also note that MrExcel has written many books, for various skills and levels.
See: https://www.mrexcel.com/store/index.php?l=product_list&c=3