find average eventhough some cell value is blank

[#1]

hi,

how can we calculate average of numbers in pivot table if some cell value is blank. average should be (x+y+z)/3 eventhough the value of z is blank.

i need suggestion please

thank you in advance

[swuehl]

If the values are blank, a

=sum(FIELD) / count(FIELD)

or avg(FIELD) should do.

But probably you are talking about NULLs, so either try to count a FIELD2 linked to your FIELD 1:1 but not having NULLs, or maybe something like

=sum(FIELD) / (count(FIELD)+nullcount(FIELD))

Hope this helps,

Stefan

[#3]

merci, i tried this but it gives 0% as average value instead of 91% if z is blank.

anyway thanks for suggestion

[swuehl]

Could you post a small sample here?

[#5]

here ia an example

[swuehl]

You are trying to build an average per record and the count of fields is 10, right?

Try as expression in script or chart:

rangesum(a,b,c,d,e,f,g,h,i,j)/10

for the average, then.

Hope this helps,

Stefan

[#7]

Hi Swuel,

the count changes for each row. for example if z is blank then count should be 9 not 10.

this time i got the value as 91% not 0%. now how to change the count value rather than keeping it fix as 10.

thank you

[swuehl]

Use

rangesum(a,b,c,d,e,f,g,h,i,j) / rangecount(a,b,c,d,e,f,g,h,i,j)

[#9]

sorry but it counts 10 and generate same value as before

[swuehl]

Can't reproduce your last post, please see attached.