Averaging data and rounding up

[John Mac]

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

 

[JLatham]

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]

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]

John,
I gave you answer on how to make sure the average is an average in my first
reply.

You can test how it is working, rounding/returning results by 'observation':
place some values in P2, S3 and V2 and observe the value returned. If it is
not as you require, then we can examine it closer and determine if we need to
change anything else.

You could break the formula out into separate cells to see what values each
section returns, as:
=MOD((P2+S2+V2)/3,1)
or
=MOD((P2+S2+V2)/3,1) < 0.3
(and I'm thinking, without testing) that is going to return TRUE or FALSE,
although it may return 1 or 0 - again, testing would reveal the answer.
to see what that formula returns and how it compares to your
desired/expected results. or even
=MOD((P2+S2+V2)/3,1) + MOD((P2+S2+V2)/3,1) + 1