Formula returning a value when no data present

**cmac7872** · 10-12-2012, 12:30 PM

Hello All,

I am using the following formula to analyze data set A and then take an average and put it into the cell where the formula is:

=SUM((LOOKUP(ROUND(B397,0),{0,6,12,15,24},{1,2,3,4,5}))+((LOOKUP(ROUND(B399,0),{0,180,300,360,420},{1,2,3,4,5})))+(LOOKUP(ROUND(B396,0),{0,75,100,150,200},{1,2,3,4,5}))+(LOOKUP(ROUND(B398,0),{0,4,6,10,20},{5,4,3,2,1})))/4

The formula works perfectly except when I clear data set A, the above formula returns a value of "2". This happens when no data is present in the cells.

Thanks for the assistance with this.

**benishiryo** · 10-12-2012, 12:42 PM

hi cmac7872. a blank is considered as "0", hence it's returning the result_vector in your LOOKUP formula. SUM(1 + 1 + 1 + 5) / 4

tell us how you want to deal with it & we can assist you.

**cmac7872** · 10-12-2012, 12:52 PM

I want the formula to return no value (""), if there is no data present in the cells.

Thanks in advance.

**ConneXionLost** · 10-12-2012, 01:07 PM

How about:

[code]
=IF(OR(B396="",B397="",B398="",B399=""),"",your formula)
[code]

**cmac7872** · 10-12-2012, 01:37 PM

Beautiful! Worked perfectly! Thanks so very much!

=IF(OR(B17="",B19="",B16="",B18=""),"",SUM((LOOKUP(ROUND(B17,0),{0,6,12,15,24},{1,2,3,4,5}))+((LOOKUP(ROUND(B19,0),{0,180,300,360,420},{1,2,3,4,5})))+(LOOKUP(ROUND(B16,0),{0,75,100,150,200},{1,2,3,4,5}))+(LOOKUP(ROUND(B18,0),{0,4,6,10,20},{5,4,3,2,1})))/4)