Tuesday, February 28, 2012

yogi_Develop Formulas For Fraction Rounding

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #449          www.energyefficientbuild.com

user theDante said:
formula errors in google docs but not excel
My fraction rounding formula works in excel, but not in google docs.
=IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),16)=0),TEXT(R2,"#"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),8)=0),TEXT(R2,"# 0/2"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),4)=0),TEXT(R2,"# 0/4"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),2)=0),TEXT(R2,"# 0/8"),TEXT(R2,"# 0/16")))))
It’s supposed to round numbers to the nearest 1/16th, but it returns “error: Unknown format text: # 0/16”
Does anyone have any ideas why google docs doesn't understand?
-----------------------------------------
following is a solution to the best of my understanding what user theDante is intending to do ... I have however generalized the formula byond just the 1/16 rounding