How to average a list of numbers according to their formats

Posted by Carmen on February 01, 2001 7:26 AM

Hi, I would like to know how to average the following
numbers according to their formatting:

e.g. In column A:
$1.00
5%

$2.00
10%

Is there a way to average up only the % in one cell, and
then average up the $ amount in another cell?

Thank you very much!

Posted by Mark W. on February 01, 2001 7:53 AM

Carmen, I suppose you'll have no trouble finding someone
who will write some VBA to fulfill your request. The
problem is that you'll have a worksheet that is difficult
to understand because it's logic is not self-evident.
For the sake of your users, managers or successors
why don't you label these values with some meaningful
terms and then use those terms in your formula.

For example, suppose that cells A1:A2, A4:A5 contain
{"Price";"Percent") and your sample values are in
corresponding cells in column B. Then you can easily
accomplish your objective by using:

{=AVERAGE(IF(A1:A5="Price",B1:B5,""))}
{=AVERAGE(IF(A1:A5="Percent",B1:B5,""))}