=IF(I6<40,"",(SUM(I6-40))) results in #VALUE!

[oomfh]

The above works great until I delete all the data (at the end of each week) that is summed in cell I6.

How do I get a blank cell to appear instead of #VALUE!

?

 

[njimack]

=MAX(I6-40,0)
Note - format cell as: 0;-0;

 

[oomfh]

=MAX(I6-40,0)
Note - format cell as: 0;-0;

Neil, I tried your formula and it didn't work - even when the data totals > 40 it still displays #VALUE!

The formula is placed in column J. Column I simply sums the data entered into columns B through H. Objective is to calculate and display hours over 40, else display a blank (including when columns B through H are blank.)

Thx

 

[oldbrewer]

sum(I6-40) what does this mean ? sum(A1:A3) sums A1 A2 A3 Sum(A1:A3) -40 sums A1 A2 A3 and deducts 40

 

[oldbrewer]

ADVERTISEMENT

If(I6>40,i6-40,"")

 

[jai9]

Hi,

It should be something like,

=IFERROR(IF(I6<40,"",(I6-40)),"")

Jai

 

[njimack]

ADVERTISEMENT

Neil, I tried your formula and it didn't work - even when the data totals > 40 it still displays #VALUE!
Thx

That would only happen if I6 contains an error or text.

 

[montecarlo2079]

IF I am understanding this correctly, all you have to add is

=if(I6="","",IF(I6<40,"",(SUM(I6-40))))

That way if I6 is blank it doesnt calculate.

 

[oomfh]

Hi,

It should be something like,

=IFERROR(IF(I6<40,"",(I6-40)),"")

Jai

Thank you Jai, this works perfectly. Thanks to all of the other replies as well. I probably could have done a better job of describing the application in my initial post.