We help IT Professionals succeed at work.

excel 2016 formula & ref errors

hi experts, could you please assist with formulas & 'ref' errors on the attached excel 2016 wksht
- all info is in the excel worksheet
ztile-wall---floor_r1.0.xlsx
Comment
Watch Question

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:

#Ref! error is produced if you delete a cell which is used in the formula cell. By deleting the cell doesn't mean deleting the cell content rather deleting the cell itself like deleting a row or a column.


Try this...

In H5

=IF(D5="","",ROUNDUP(C5*D5/$G$3,0)*ROUNDUP(C5*E5/$H$3,0))

and copy it down.

Frank .SBuilding Estimator

Author

Commented:
thanks subodh, it appears I have missed to add the % column in the formula for cell H5, could you please check & add.

Would this work?

=IF(D5="","",ROUNDUP(C5*D5/$G$3,0)*ROUNDUP(C5*E5/$H$3,0))+g5%
the answer should = 24 (rounded up from 23.1)
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:

Try this...

=IF(D5="","",ROUNDUP(ROUNDUP(C5*D5/$G$3,0)*ROUNDUP(C5*E5/$H$3,0)*(1+G5%),0))

Frank .SBuilding Estimator

Author

Commented:
hi subodh, cell h5 & h6 does not seem to be calculating correctly, please see re-attached worksheet.

cell h5 should =

d5/g3 *c5 +g5  therefore 0.9/0.4=2.25x1 +5% = 2.36 therefore 3.0 tiles
e5/h3 *c5 +g5  therefore 2.0/0.4=5x1 +5% = 5.25 therefore 6.0 tiles
3.0 x 6.0 = 18.0 tiles



cell h6 should =

d6/g3 *c6+g6 therefore 0.9/0.4x 2+5% = 4.72, therefore 5.0
e6/h3 *c6+g6 therefore 2.0/0.4x 2+5% = 10.5, therefore 11.0
5.0 x 11.0 = 55.00 tiles
ztile-wall---floor_r1.1.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:

If you know the output you may tweak the formula as required.

As per your desired output, the formula should be...

=IF(D5="","",ROUNDUP((D5/$G$3*C5)+G5%,0)*ROUNDUP((E5/$H$3*C5)+G5%,0))