AVERAGE Function

[Richie]

IN COL G ROW 1 I HAVE THE FOLLOWING: =A1

IN COL H ROW 1 I HAVE THE FOLLOWING: =B1

BOTH HAVE BEEN FILLED IN DOWN TO G18 AND H18 (ALL CELLS
NOW HAVE 0 IN THEM)

I TYPE IN 20 IN A1 THRU A5 AND 10 IN B1 THRU B5 AND THOSE
VALUES APPEAR IN G1 THRU G5 AND H1 THRU H5

IN G19 I HAVE =SUM(G1:G10) AND IN H19 I HAVE =SUM
(H1:H10).THIS GIVES ME THE CORRECT TOTAL.

IN G20 I HAVE =AVERAGE(G1:G10) AND IN H19 I HAVE =AVERAGE
(H1:H10) BUT I DON'T GET THE RGHT AVERAGE WHICH SHOULD BE
20 AND 10. I DETERMINED ITS THE 0's IN THE COLUMNS THAT
THROWS OFF THE AVERAGE. IF I DELETE THE ZEROS THE AVERAGE
WILL CORRECT ITSELF BUT WHEN I ENTER NUMBERS IN COL A AND
B THEY WONT CARRY OVER TO G AND H ANYMORE. DOES ANYONE
HAVE A SOLUTION TO THIS? THANKS FOR THE HELP....Richie

 

[Frank Kabel]

Hi
first: please turn off your CAPSLock: Difficult to read and considered
as shouting

Now to your question:
1. Option:
change the formulas in G1 / H1 to
G1: =IF(A1<>"",A1,"")
H1: =IF(B1<>"",B1,"")
and copy both down. Now the Average function should work

2. If your values in column A+B can NEVER be zero you could leave the
formulas and change the Average formula. Use the following array
formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(G1:G18<>0,G1:G18))
and
=AVERAGE(IF(H1:H18<>0,H1:H18))

 

[Kevin H. Stecyk]

Richie wrote...

IN COL G ROW 1 I HAVE THE FOLLOWING: =A1

IN COL H ROW 1 I HAVE THE FOLLOWING: =B1

BOTH HAVE BEEN FILLED IN DOWN TO G18 AND H18 (ALL CELLS
NOW HAVE 0 IN THEM)

I TYPE IN 20 IN A1 THRU A5 AND 10 IN B1 THRU B5 AND THOSE
VALUES APPEAR IN G1 THRU G5 AND H1 THRU H5

IN G19 I HAVE =SUM(G1:G10) AND IN H19 I HAVE =SUM
(H1:H10).THIS GIVES ME THE CORRECT TOTAL.

IN G20 I HAVE =AVERAGE(G1:G10) AND IN H19 I HAVE =AVERAGE
(H1:H10) BUT I DON'T GET THE RGHT AVERAGE WHICH SHOULD BE
20 AND 10. I DETERMINED ITS THE 0's IN THE COLUMNS THAT
THROWS OFF THE AVERAGE. IF I DELETE THE ZEROS THE AVERAGE
WILL CORRECT ITSELF BUT WHEN I ENTER NUMBERS IN COL A AND
B THEY WONT CARRY OVER TO G AND H ANYMORE. DOES ANYONE
HAVE A SOLUTION TO THIS? THANKS FOR THE HELP....Richie

Richie,

Please don't write in all CAPS. It seems like you're screaming at us.

I think what you want to do is...

G1=if(isnumber(A1), "", A1) then Copy down
H1=if(isnumber(B1), "", B1) then Copy down

Try that and see it it works.

Hope it helps.

Kevin

 

[Kevin H. Stecyk]

Frank Kabel...

I seem to be following you this morning by posting immediately after you.
It's not intentional, honest. It's just I see a question that I can answer
and then I see your answer after my answer has already been launched into
cyberspace.

Best regards,
Kevin

 

[Kevin H. Stecyk]

Kevin H. Stecyk wrote

I think what you want to do is...

G1=if(isnumber(A1), "", A1) then Copy down
H1=if(isnumber(B1), "", B1) then Copy down

It is wrong..it ought to be

G1=if(isnumber(A1), A1, "") then Copy down
H1=if(isnumber(B1), B1, "") then Copy down

There, that's better.

 

[Richie]

-----Original Message-----
Hi
first: please turn off your CAPSLock: Difficult to read and considered
as shouting

Now to your question:
1. Option:
change the formulas in G1 / H1 to
G1: =IF(A1<>"",A1,"")
H1: =IF(B1<>"",B1,"")
and copy both down. Now the Average function should work

2. If your values in column A+B can NEVER be zero you could leave the
formulas and change the Average formula. Use the following array
formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(G1:G18<>0,G1:G18))
and
=AVERAGE(IF(H1:H18<>0,H1:H18))


--
Regards
Frank Kabel
Frankfurt, Germany



.
Thanks for the help....