We help IT Professionals succeed at work.

# value error - if statement with ceiling function

on
hi experts, im having problems with an ifstatement with ceiling function (see below), it is producing a VALUE error?
basically if the range cells = zero or nothing then show nothing otherwise sum the range & roundup in increment of 0.2

Comment
Watch Question

## View Solutions Only

Commented:

what if you trying this?

`=IF(H6:H10="","",SUM(CEILING(H6:H10,0.2)))`
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I believe you can use the following:
``````=IFERROR(1/(1/CEILING(SUM(H6:H10),0.2)),"")
``````

This formula returns an empty string (looks like a blank) if the values in H6:H10 are are either 0 or blank. The rounding up done by the CEILING function is done on the sum of the values in H6:H10 rather than each of those values individually.
Building Estimator

Commented:
thankyou but it seems there is also a problem with h6 which should = 2.00 not 1.95,
formula is;
=IF(D6="","",F6*G6)
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
``````=IFERROR(IF(D6="","",CEILING(F6*G6,0.2)),"")
``````
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you do the CEILING bit on each cell in H6:H10, then you can change the formula in H11 to:
``````=IF(SUM(H6:H10)=0,"",SUM(H6:H10))
``````
Building Estimator

Commented: