Averages

[JB]

I have a column (M) that used to get its value from an earlier column's (E)
value dvided by 18. The, a further column N) worked out the verage of all
values in that column.

So:

E =IF(ISERROR(AVERAGE(A5:C5)),"",AVERAGE(A5:C5))

M = E/18

N =AVERAGE(M4:M1000)


Now: I changed M to become =IF(ISERROR(AVERAGE(I4:K4)),"",AVERAGE(I4:K4))

and expected N to stay as before. However, N now gives me a totally
unexpected result: 3.1 instead of 5.9.

What have I done wrong?

Thank you

JB

 

[JB]

I think I found half the problem, but can't understand why it is so.

M =IF(ISERROR(AVERAGE(I4:K4)),"",AVERAGE(I4:K4))

The idea above, as before, is for M to ignore empty values of I, J or K and
average just what is there, i.e. I = 6, J = 4, K = empty. Average should be
=5

Somehow, at the moment the empty cells are being taken into consideration
and I suspect the same is happening to N

Help!....

JB

 

[Roger Govier]

Hi

I am totally lost with what you are saying.
Firstly you say that that M is the Average of A:C divided by 18
Then you changed M to be the Average of I:K, yet you expected the answer to
be the same.
Why? I wouldn't expect the result to be the same unless the figures in I:K
did happen to be the same as A:C/18.

You then talk about N, which is the average of a long column, whereas all
your other averages are row based.

 

[JB]

Dear Roger

Yes, I expected the answer to be the same because E was the average of A;B;C
in mg/dl. M was E divided by 18 to convert to mmol/l (these are blood glcose
levels). And N was the average of M.

M stopped being the mg/dl to be directly mmol/l. So, the value of M is the
same.

My first problem is that the original formula
=IF(ISERROR(AVERAGE(A5:C5)),"",AVERAGE(A5:C5)) which worked out the average
for mg/dl does not seem to work when I transfer it to look at I;G:K which is
already in mmol/l. All I did wa to copy the first formula and change the
row letters. It should still work igniring empty cells, but it does not. f
I manage to solve this one, maybe the other problem N =AVERAGE(M4:M1000)
will sort itsel out.

Thanks for your help

B

 

[Roger Govier]

Hi

The formulae work fine for me on my system.
Would you like to send me your workbook and I will see if I can figure out
what is going wrong for you.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address