AVERAGE calculation differs when alone or in a complex formula?

users
#1

Hi,

I'm trying to figure out why differs AVERAGE calculations when using it
alone in a cell or when using it in a complex formula?

http://nabble.documentfoundation.org/file/n3618892/Average_Problem.ods
Average_Problem.ods

In the .ods abowe on the sheet '17.' one can see that that AVERAGE
calculations in the
'AD30'
=IF(OR(COUNTIF(AD6:AD17;"=1");COUNTIF(AD19:AD20;"=1");COUNTIF(AD29;"=1"));"0";AVERAGE(AC6:AC17;AC19:AC20;AD29))

and in the

'AD46'
=AVERAGE(AD6:AD17;AD19:AD20;AD29)

cells are different. How could it be?

#2

Because these averages formulas refers to different ranges. Take a look (best
viewed with monospace font):
AVERAGE(AC6:AC17;AC19:AC20;AD29)
AVERAGE(AD6:AD17;AD19:AD20;AD29)
                  ^ ^ ^ ^

If you adjust ranges in complex formula to be the same as ranges in simpler
formula, they will produce the same output.

#3

Pal,

Hi,

I'm trying to figure out why differs AVERAGE calculations when using it
alone in a cell or when using it in a complex formula?

http://nabble.documentfoundation.org/file/n3618892/Average_Problem.ods
Average_Problem.ods

In the .ods abowe on the sheet '17.' one can see that that AVERAGE
calculations in the
'AD30'
=IF(OR(COUNTIF(AD6:AD17;"=1");COUNTIF(AD19:AD20;"=1");COUNTIF(AD29;"=1"));"0";AVERAGE(AC6:AC17;AC19:AC20;AD29))

and in the

'AD46'
=AVERAGE(AD6:AD17;AD19:AD20;AD29)

One is (mostly) averaging the data in column AC and the other is using column AD. Since the data in each column is not the same the answers will be different.

#4

Jay,

Jay Lozier wrote

I'm trying to figure out why differs AVERAGE calculations when using it
alone in a cell or when using it in a complex formula?

http://nabble.documentfoundation.org/file/n3618892/Average_Problem.ods
Average_Problem.ods

In the .ods abowe on the sheet '17.' one can see that that AVERAGE
calculations in the
'AD30'
=IF(OR(COUNTIF(AD6:AD17;"=1");COUNTIF(AD19:AD20;"=1");COUNTIF(AD29;"=1"));"0";AVERAGE(AC6:AC17;AC19:AC20;AD29))

and in the

'AD46'
=AVERAGE(AD6:AD17;AD19:AD20;AD29)

One is (mostly) averaging the data in column AC and the other is using
column AD. Since the data in each column is not the same the answers
will be different.

--
Jay Lozier
jslozier@

Indeed, I was inattentive here. Thanks!

http://nabble.documentfoundation.org/file/n3619267/Average_Problem.ods
Average_Problem.ods

However, as one can see in the file abowe (that I upload again, modified of
course) that AVERAGE still calculate slightly different when are used in
complex formula as if it used alone.

Eg. in the abowe file one can see on the '19.' sheet that cell 'AD30' with
the same AVERAGE formula, that is in a complex formula calculate the value
'4,07' but the cell 'AD46' calculate with the same AVERAGE formula when is
alone the value '4,13'. How could that be?

#5

Jay,

Jay Lozier wrote

I'm trying to figure out why differs AVERAGE calculations when using it
alone in a cell or when using it in a complex formula?

http://nabble.documentfoundation.org/file/n3618892/Average_Problem.ods
Average_Problem.ods

In the .ods abowe on the sheet '17.' one can see that that AVERAGE
calculations in the
'AD30'
=IF(OR(COUNTIF(AD6:AD17;"=1");COUNTIF(AD19:AD20;"=1");COUNTIF(AD29;"=1"));"0";AVERAGE(AC6:AC17;AC19:AC20;AD29))

and in the

'AD46'
=AVERAGE(AD6:AD17;AD19:AD20;AD29)

One is (mostly) averaging the data in column AC and the other is using
column AD. Since the data in each column is not the same the answers
will be different.

--
Jay Lozier
jslozier@

Indeed, I was inattentive here. Thanks!

http://nabble.documentfoundation.org/file/n3619267/Average_Problem.ods
Average_Problem.ods

However, as one can see in the file abowe (that I upload again, modified of
course) that AVERAGE still calculate slightly different when are used in
complex formula as if it used alone.

Eg. in the abowe file one can see on the '19.' sheet that cell 'AD30' with
the same AVERAGE formula, that is in a complex formula calculate the value
'4,07' but the cell 'AD46' calculate with the same AVERAGE formula when is
alone the value '4,13'. How could that be?

Take a look again. They are still not the same (AD20 ≠ AD21).

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#6

Jay,

Jay Lozier wrote

I'm trying to figure out why differs AVERAGE calculations when using it
alone in a cell or when using it in a complex formula?

http://nabble.documentfoundation.org/file/n3618892/Average_Problem.ods
Average_Problem.ods

In the .ods abowe on the sheet '17.' one can see that that AVERAGE
calculations in the
'AD30'
=IF(OR(COUNTIF(AD6:AD17;"=1");COUNTIF(AD19:AD20;"=1");COUNTIF(AD29;"=1"));"0";AVERAGE(AC6:AC17;AC19:AC20;AD29))

and in the

'AD46'
=AVERAGE(AD6:AD17;AD19:AD20;AD29)

One is (mostly) averaging the data in column AC and the other is using
column AD. Since the data in each column is not the same the answers
will be different.

--
Jay Lozier
jslozier@

Indeed, I was inattentive here. Thanks!

http://nabble.documentfoundation.org/file/n3619267/Average_Problem.ods
Average_Problem.ods

However, as one can see in the file abowe (that I upload again, modified of
course) that AVERAGE still calculate slightly different when are used in
complex formula as if it used alone.

Eg. in the abowe file one can see on the '19.' sheet that cell 'AD30' with
the same AVERAGE formula, that is in a complex formula calculate the value
'4,07' but the cell 'AD46' calculate with the same AVERAGE formula when is
alone the value '4,13'. How could that be?

Take a look again. They are still not the same (AD20 ≠ AD21).

Sorry for confusing. I mean that AD19:AD20 ≠ AD19:AD21. The first one
is two cells (4,5), the second one three (4,5,5).

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#7

Johnny Rosenberg wrote

2011/12/29 csanyipal <csanyipal@>:

Jay Lozier wrote

Indeed, I was inattentive here. Thanks!

http://nabble.documentfoundation.org/file/n3619267/Average_Problem.ods
Average_Problem.ods

However, as one can see in the file abowe (that I upload again, modified
of
course) that AVERAGE still calculate slightly different when are used in
complex formula as if it used alone.

Take a look again. They are still not the same (AD20 ≠ AD21).

Indeed, it seems that that I work to much and my attention abates. It's time
to rest a little. Thanks for the support!

#8

...

Indeed, I was inattentive here. Thanks!

http://nabble.documentfoundation.org/file/n3619267/Average_Problem.ods
Average_Problem.ods

However, as one can see in the file abowe (that I upload again, modified of
course) that AVERAGE still calculate slightly different when are used in
complex formula as if it used alone.

Eg. in the abowe file one can see on the '19.' sheet that cell 'AD30' with
the same AVERAGE formula, that is in a complex formula calculate the value
'4,07' but the cell 'AD46' calculate with the same AVERAGE formula when is
alone the value '4,13'. How could that be?

...
Because they are not the same formula:
The AVERAGE portion of AD30 is:
AVERAGE($AD$6:$AD$17,$AD$19:$AD$20,$AD$29)
The AVERAGE portion of AD46 is:
=AVERAGE($AD$6:$AD$17,$AD$19:$AD$21,$AD$29)

Look carefully and you will see the difference.

If I change AD46 to:
AVERAGE($AD$6:$AD$17,$AD$19:$AD$20,$AD$29)
the result is the same as AD30

#9

Johnny Rosenberg wrote

2011/12/29 csanyipal<csanyipal@>:

Jay Lozier wrote
Indeed, I was inattentive here. Thanks!

http://nabble.documentfoundation.org/file/n3619267/Average_Problem.ods
Average_Problem.ods

However, as one can see in the file abowe (that I upload again, modified
of
course) that AVERAGE still calculate slightly different when are used in
complex formula as if it used alone.

Take a look again. They are still not the same (AD20 ≠ AD21).

Indeed, it seems that that I work to much and my attention abates. It's time
to rest a little. Thanks for the support!

I know these errors are often hard to properly track down. When I have done something similar I knew I had entered the formula correctly but had make a minor change with big results.

One way to avoid this is to use named ranges. Say C2:C34 is named scores then use the name in the formula.

#10

Jay Lozier wrote

Johnny Rosenberg wrote

2011/12/29 csanyipal<csanyipal@>:

Jay Lozier wrote
Indeed, I was inattentive here. Thanks!

However, as one can see in the file abowe (that I upload again,
modified
of course) that AVERAGE still calculate slightly different when are
used in
complex formula as if it used alone.

Take a look again. They are still not the same (AD20 ≠ AD21).

Indeed, it seems that that I work to much and my attention abates. It's
time
to rest a little. Thanks for the support!

I know these errors are often hard to properly track down. When I have
done something similar I knew I had entered the formula correctly but
had make a minor change with big results.

One way to avoid this is to use named ranges. Say C2:C34 is named scores
then use the name in the formula.

Thank you very much for help!
(I want to thank you in private e-mail but this features doesn't work rigth
now.)