It seems that my formula:

=INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1)

will not calculate the average of the cells if one of the cells is blank, is
there any way of getting around this?
Also has does this formula round of the answer, does it use the nearest to
0.1, 0.5, 0.9 or does it round up /down?

"JLatham" wrote:

Instead of using the AVERAGE() function, do it manually in each section of
the formula where you're using it now:
AVERAGE(P2,S2,V2)
becomes
((P2+S2+V2)/3)
That will treat empty cells as zero. It may or may give you the results you
want.
Given P2 = 1, S2 = 2, V2 = 3 then both formulas will return 2 (6/3)
But Given P2=1 S2 empty, V2 = 3 then AVERAGE() will give 2 (4/2) while the
manual method would give 1.33333 (4/3).


"John Mac" wrote:

I am currently using the following formula as this averages up my data to
either 0.1, 0.5 or 0.9 (I hope!):

=INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1)

If the cells I am averaging do not contain data then the formula does not
make a calculation. Is there a way the formula can still calculate the
average even if a cell does not contain data?

Many thanks for your help
John