Averages of absolute values

[Rob]

Hi
I'm switching over from QuattroPro to EXCEL and am having some newbie
problems.

I have a list positive and negative numbers (A1..A25). I would like to
calculate the average of the absolute values. The only method I have been
able to come up with is to calculate the absolute values in column B using
B1=ABS(A1), B2=ABS(A2), etc. Then use A26=AVERAGE(B1..B25) and hide column
B. Is there a better way to do this?

In Quattro Pro I would use @AVG(@ABS(A1..A25)), but a similar method doesn't
seem to work in Excel.

Thank you,
Rob

 

[Trevor Shuttleworth]

Ron

Array enter the following formula:

=SUM(ABS(A1:A25))

Array Enter means press Ctrl-Shift -Enter to input the formula rather than
just Enter

This will look like {=SUM(ABS(A1:A25))} but you can't just type the curly
brackets.

Regards

Trevor

 

[SteveG]

Rob,

Use this array formula,

=AVERAGE(ABS(A1:A10))

After typing in the formula, commit with Ctrl-Shift-Enter
simultaneously instead of just Enter. Your formula will look like this
in the formula bar afterwards.

{=AVERAGE(ABS(A1:A9))}

Don't enter the {} yourself.

HTH

Steve

 

[SteveG]

Rob,

Change your ranges as needed. (A1:A25)

Steve

 

[Peo Sjoblom]

=AVERAGE(IF(A1:A25<>"",ABS(A1:A25)))

needs to be entered with ctrl + shift & enter

if there can't be any blank cells in A1:A25 you can use


=AVERAGE(ABS(A1:A25))

also array entered



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Bob Phillips]

=AVERAGE(IF(A1:A25<>"",ABS(A1:A25)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Trevor Shuttleworth]

Sorry, that should have been:

=AVERAGE(ABS(A1:A25)) array entered

Regards

Trevor

 

[Beege]

Hi
I'm switching over from QuattroPro to EXCEL and am having some newbie
problems.

I have a list positive and negative numbers (A1..A25). I would like to
calculate the average of the absolute values. The only method I have been
able to come up with is to calculate the absolute values in column B using
B1=ABS(A1), B2=ABS(A2), etc. Then use A26=AVERAGE(B1..B25) and hide
column B. Is there a better way to do this?

In Quattro Pro I would use @AVG(@ABS(A1..A25)), but a similar method
doesn't seem to work in Excel.

Thank you,
Rob

Rob,
Try
=AVERAGE(ABS(A1:A25))
This is going to be an arry, so commit the cell using ctrl-shft-enter (excel
will {bracket} the formula).

Beege

 

[Rob]

Thank you, everyone. That worked perfectly.
Is there a tutorial available to show when, where and how arrays are used?

Thank you,
Rob

 

[Trevor Shuttleworth]

Rob

Search Google for: using array in excel

One example:

http://www.meadinkent.co.uk/xlarrays.htm

Regards

Trevor