We help IT Professionals succeed at work.

value error - if statement with ceiling function

Frank .S
Frank .S asked
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

ifStatement with ceiling function-excel 2016
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:

what if you trying this?


=IF(H6:H10="","",SUM(CEILING(H6:H10,0.2)))
byundtMechanical 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)),"")

Open in new window


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.
Frank .SBuilding Estimator

Author

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)),"")

Open in new window

byundtMechanical 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))

Open in new window

Frank .SBuilding Estimator

Author

Commented:
thankyou experts for your solutions
Frank .SBuilding Estimator

Author

Commented:
yes the ceiling formula is in each cell h6:h10