macro ?

  • Thread starter Thread starter Tomek
  • Start date Start date
T

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
 
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.
 
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
 
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
 
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
 
U¿ytkownik "JE McGimpsey said:
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),"")
Click to expand...
What if zero is also a data , what to do to change it to avoid empty cells
?
Tomek
 
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).
 
Back
Top