What were you trying to do with your original formula?

What Tom suggests will give an average but it may not
be what you want as you did not clarify it.

What his formula gives is the average of each pair of
numbers in your two ranges, C2:J2 and C4:J4. It
divides each number in row 4 by the number in that
column in row 2 and then averages these results. It
then multiplies that average by 100.

Specifically what his formula does is gets the number
in each cell in row 4 and tests to see if there is something
in the cell. If there is then it is TRUE and if not then it is
FALSE. It does this with row 2 also. It just happens
that in Excel (and most other computer applications)
that TRUE = 1 and FALSE = 0. It then multiplies these
two numbers together and give one of
0*0
0*1
1*0
1*1

The only one of those to give an answer of 1 is the last one.
That is for any pair of cells in rows 2 and 4 where there is
a number in both.

As 1 = TRUE then when there is a number in both rows of
a column then the next bit of the formula, the bit after the
comma, is the bit that is used. If there is a 0 then FALSE
is used. The final result is of this part is then multiplied
by 100.

=AVERAGE( of each pair of numbers from rows 2 and 4 )*100

where, if there is a number in row 2 and 4 in the column

each pair of numbers from rows 2 and 4 is the number in row 4
divided by the number in row 2

otherwise

0



Entering it with ATL+Enter gives an array formula so you get
more than one result - one per each cell in the range - and you
average all these numbers.

Does that help you understand the formula? Is that what you want?
If it is not what you want then tell us the numbers you have as an
example and what result you want and it will probably just mean
shuffling things in Tom's formula a bit.

Chrissy.



"Eric G" wrote in message ...
Thanks Tom,

I haven't tried it yet. I'm just wondering what the
IF((C4:J4<"")* <------ multiplication (*) sign is for?
Not really sure what the first two parts of the formula are for.

Thanks Eric


On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy"
wrote:

=AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100

Entered with Ctrl+shift+enter rather than just Enter because this is an
array formula

Should do what you want.

--
Regards,
Tom Ogilvy



Eric G wrote in message
...
I'd really appreciate getting some help with one formula I'm working
on, if possible.

Something like:

AVERAGE(C4:J4/C$2:J$2*100)

Right now I sort of have it working.
The only problem is that with values at the moment in only C4:E4
it's giving me a 'false' average.

I could restrict the formula to the cells that have values but it
would be very tedious to keep changing it each time I add a value to
F4, G4 etc.

TIA Eric