# excel 2016 formula &amp; 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
Microsoft ExcelMicrosoft Office

Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

#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 .S

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)

Try this...

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

Frank .S

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