Help with formula?

[Eric G]

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

 

[Chrissy]

The formula
= AVERAGE( range )

will return the average of the numbers in that range. It is identical to the
formula

= SUM ( range ) / COUNT ( range )

Your formula does not average anything. If I put your formula in a cell in
column C then I get results that could be obtained by

= C4/C2 * 100

If I copy that across to all cells in that row in cols C to J I get the same
results that you get. You do say that you are not getting the correct results
but do not say what is wrong or what you want.

I cannot work out why you are using AVERAGE anyway.

How about you say what values you have and what results you want and
where these values are.

What I think you have is a table with values in the top row and you want
to get results from different rows divided by the top row value - but that
it just a guess.

Chrissy.


Eric G wrote

 

[Tom Ogilvy]

=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.

 

[Eric G]

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

 

[Tom Ogilvy]

(C4:J4<>"")

C4: 100
D4: 3
E4:
F4:
G4: 10
H4:
J4:

produces {True,True,False,False,True,False,False}

C2: 1000
D2:
E2: 300
F2:
G2: 50
H2:
J2: 1000

produces {True,False,True,False,True,False,True}

when we multiply these together, the Trues are coerced to 1's and the Falses
are coerced to zeros

{1,1,0,0,1,0,0} * {1,0,1,0,1,0,1} = {1,0,0,0,1,0,0}

the if statement interprets 1 as True and zero as False, so the if returns

{100/1000,False,False,False,10/50,False,False}

The Average function ignores the False and takes the average of 100/1000 and
10/50

You can go to the formula bar and highlight complete subexpressions and the
formula and do F9 to have that subexpression evaluated (hit esc to return
the formula, don't hit enter or the subexpression will be replaced with the
evaluated values).

--
Regards,
Tom Ogilvy

 

[Eric G]

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

 

[Chrissy]

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.

 

[Dave Peterson]

In the formula bar, highlight c4:j4<>""
then hit F9.
You'll see something like:
{FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE}

Now do the same thing with C2:j2<>"" and hit f9
You'll see the same kind of thing:
{FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE}

Hit escape (to reset the formula).

Now hightlight (C4:J4<>"")*(C2:J2<>"")
and hit F9

You'll see something like:
{0,1,0,1,0,1,0,0}

The formula is doing an "AND". The 0's are when either are false, the 1's are
when both are True.

So Tom's formula kind of gets translated into:

For each element (c4, d4, e4, ...,J4 and c2, ....,j2) look to see if both are
non-blank. If they are non-blank the use the division (X4/X2--C2&4, D2&4, etc).

If either is blank (or both are blank), then use False (not explicitly included
in Tom's original formula, but there implicitly).

(And False doesn't upset the Average calculation.)

Then multiply the whole thing by 100.

 

[Tom Ogilvy]

My explanation isn't visible ?

 

[Eric G]

Hi Dave,

Thanks very much for your great detailed explanation!
I understood it all and am ready to give it a whirl now.

Much appreciated! Eric

 

[Dave Peterson]

Only if I looked! (I didn't see it.)

My explanation isn't visible ?

<<snipped>>

 

[Eric G]

Hi Tom,

I tried out your formula yesterday and it worked very nicely. Thanks
again for your help!

Eric

 

[Eric G]

Thanks Chrissy,

Tom helped me out with the formula and I've got it working now.

Eric