# Thread: Excel Average Formula Needed (Excel 2002)

1. ## Excel Average Formula Needed (Excel 2002)

I need to create a formula to calculate an average. The problem is that I want an "X" to show in the cell but equal a number and that number is used in the calculation.

For example, any time an "X" shows in column B, it equals 7.0; and X in column C equals 5.0, etc. The formula uses the values to calculate the average.

I've attached a sample file showing what I need.

Thanks so much!

2. ## Re: Excel Average Formula Needed (Excel 2002)

See if the attached version does what you want. I inserted a new row 2, entered the weights in B2:I2, then hid this row.
The formula in J3 is
<code>=IF(COUNTA(B3:I3)=0,"",SUMIF(B3:I3,"X",\$B\$2: \$I\$2)/COUNTA(B3:I3))</code>
and this was filled down.

3. ## Re: Excel Average Formula Needed (Excel 2002)

That helped greatly. Thanks Hans!

4. ## Re: Excel Average Formula Needed (Excel 2002)

Using Han's extra row, entered as an array,

=AVERAGE(IF(B3:I3="x",\$B\$2:\$I\$2))

Caveat,

Produces a #DIV/0! error when all cells in the range are blank

This one's a litle longer, array entered also

=IF(COUNTA(B3:I3)=0,"",AVERAGE(IF(B3:I3="x",\$B\$2:\$ I\$2)))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•