macro ?

[Tomek]

Hi!
I wonder how to get a macro that counts a mean and median on the right side
of the row.
I have many worksheets filled with data.
I would like the macro to take into account only the rows that are filled
with data.
In the first column there are the names of each row.

Thank you very much in advance.

Tomek

 

[JE McGimpsey]

You don't need a macro. AVERAGE() and MEDIAN() ignore blank cells, and
you can trap for blank rows:

Assume your data is in columns B:L. Then enter

M1: =IF(COUNT(B1:L1)>0,AVERAGE(B1:L1),"")
N1: =IF(COUNT(B1:L1)>0,MEDIAN(B1:L1),"")

Copy M1:N1 down as far as necessary.

 

[Tomek]

Thank you very much but when you have many worksheets with different number
of rows filled , I think
it would be much easier and would safe much time.

Tomek

 

[Frank Kabel]

Hi
not really
VBA would be much slower and you can always use a formula such as
=AVERAGE(1:1)
even blank cells will be ignored

 

[Tomek]

of course you are right , but when you open a new worksheet with data you
have to type the functions again but when you have a good macro you just run
it.
Tomek

 

[Tomek]

You don't need a macro. AVERAGE() and MEDIAN() ignore blank cells, and
you can trap for blank rows:

Assume your data is in columns B:L. Then enter

M1: =IF(COUNT(B1:L1)>0,AVERAGE(B1:L1),"")
N1: =IF(COUNT(B1:L1)>0,MEDIAN(B1:L1),"")

What if zero is also a data , what to do to change it to avoid empty cells
?
Tomek

 

[Frank Kabel]

Hi
no need for either. The first statement in the IF clause just checks if
there's at least on number in your range (to prevent a #DIV/0 error).