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

#### oomfh

##### Board Regular
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!

?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### njimack

##### Well-known Member
=MAX(I6-40,0)
Note - format cell as: 0;-0;

#### oomfh

##### Board Regular
=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

##### Well-known Member
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

##### Well-known Member
If(I6>40,i6-40,"")

#### jai9

##### Active Member
Hi,

It should be something like,

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

Jai

#### njimack

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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.

Replies
2
Views
216
Replies
10
Views
137
Replies
2
Views
144
Replies
3
Views
109
Replies
0
Views
244

1,195,721
Messages
6,011,296
Members
441,600
Latest member
Ramatu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back