=AVERAGE (see underlying values?)

[Jeff]

I am a novice at Excel 2007. Pete_UK has been very kind in helping me
use the =AVERAGE formula.

I have a new but related question:
In the cell that contains the calculated average, is there an XL tool or
something that would show the underlying numbers in the calculated average?

I do not mean the formula or the cell addresses used. I mean the actual
numbers.

Example:
If A2, R2, AA2 contain 2, 4, 6, the calculated average will be 4. Can I
click on the cell containing the calculated "4" and see that XL obtained
this from "2,4,6"?

Jeff

 

[Clif McIrvin]

I am a novice at Excel 2007. Pete_UK has been very kind in helping me
use the =AVERAGE formula.

I have a new but related question:
In the cell that contains the calculated average, is there an XL tool
or something that would show the underlying numbers in the calculated
average?

I do not mean the formula or the cell addresses used. I mean the
actual numbers.

Example:
If A2, R2, AA2 contain 2, 4, 6, the calculated average will be 4. Can
I click on the cell containing the calculated "4" and see that XL
obtained this from "2,4,6"?

Jeff

Select the cell with the formula, then click on the Fx on the formula
bar ... the dialog that pops up should show you the input values to the
formula.

 

[Dave Peterson]

So your formula looks something like:
=average(a1:aa2)
or
=average(2:2)

I don't think that there's anything that will do what you want.

Even if the formula were something like:
=average(a2,r2,aa2)
I don't know of anything that will show you the values within each cell.

You could select each address (one at a time), then hit F9 to see its value.

Remember to hit the escape key (or edit|Undo) if you accidentally update the
formula with the value!

 

[joeu2004]

In the cell that contains the calculated average, is
there an XL tool or something that would show the
underlying numbers in the calculated average?

[....]
Select the cell with the formula, then click on the Fx
on the formula bar ... the dialog that pops up should
show you the input values to the formula.

Interesting. I never use these tools ;-).

But caveat emptor! In XL2003 at least, when A1:A3 contains 1,2,3, A4
is blank, A5:A6 contains 5,6, and B1 contains =AVERAGE(A1:A6),
selecting B1 and clicking on Fx shows that "input" is {1;2;3;0;4;5}.

Note the zero. That is misleading; it should be blank or something.
Putting an actual zero into A4 gives a different result in B1, as it
should. But clicking on Fx shows the same "input".

Is this fixed in XL2007 or XL2010?

 

[Jeff]

Select the cell with the formula, then click on the Fx on the formula
bar ... the dialog that pops up should show you the input values to the
formula.

WOW! That is really cool. Just what I needed. Thanks.
Is this also available in XL 2002? I need to send this to some people
who may only have the earlier versions.

 

[Jeff]

So your formula looks something like:
=average(a1:aa2)
or
=average(2:2)

I don't think that there's anything that will do what you want.

Even if the formula were something like:
=average(a2,r2,aa2)
I don't know of anything that will show you the values within each cell.

You could select each address (one at a time), then hit F9 to see its
value.

Remember to hit the escape key (or edit|Undo) if you accidentally update
the formula with the value!

I did not think there would be a way either but asked just in case.
Seems Fx in the Formula ribbon does it. See Clif's reply.

 

[Clif McIrvin]

In the cell that contains the calculated average, is
there an XL tool or something that would show the
underlying numbers in the calculated average?

[....]
Select the cell with the formula, then click on the Fx
on the formula bar ... the dialog that pops up should
show you the input values to the formula.

Interesting. I never use these tools ;-).

But caveat emptor! In XL2003 at least, when A1:A3 contains 1,2,3, A4
is blank, A5:A6 contains 5,6, and B1 contains =AVERAGE(A1:A6),
selecting B1 and clicking on Fx shows that "input" is {1;2;3;0;4;5}.

Note the zero. That is misleading; it should be blank or something.
Putting an actual zero into A4 gives a different result in B1, as it
should. But clicking on Fx shows the same "input".

Is this fixed in XL2007 or XL2010?

 

[Clif McIrvin]

WOW! That is really cool. Just what I needed. Thanks.
Is this also available in XL 2002? I need to send this to some people
who may only have the earlier versions.

Try it and see. I'm guessing that tool's been around for some time-
when I first needed to start learning MS Office tools our office was on
2003.