average price of 4 columns of figures but some cells can be blank
 

i need a formula to give me an average price of 4 columns of prices on 4
sheets (30 cells per column)where some cells can be blank,and even some
sheets blank,All cells are formatted as financial but no zeros showing.
-----Is it possible with a macro and if so formula please.
--
BD3

average price of 4 columns of figures but some cells can be bl
 

Thanks for your input,but ive got 4 columns on each sheet would it be best to
use your formula but 4 times
--
BD3


"bpeltzer" wrote:

The average function will ignore blanks (that is, it won't count them in the
denominator, so you wind up with an average of the non-blank cells). You
probably just need something like
=average(Sheet1!A1:A30,Sheet2!A1:A30,Sheet3!A1:A30 ,Sheet4!A1:A30)

"bigdaddy3" wrote:

i need a formula to give me an average price of 4 columns of prices on 4
sheets (30 cells per column)where some cells can be blank,and even some
sheets blank,All cells are formatted as financial but no zeros showing.
-----Is it possible with a macro and if so formula please.
--
BD3

average price of 4 columns of figures but some cells can be bl
 

If the columns are contiguous, you could do something like
=average(Sheet1!A1:D30,Sheet2!A1:D30,Sheet3!A1:D30 ,Sheet4!A1:D30); if not
then you can call out each range within a single average function:
=average(Sheet1!A1:A30,Sheet1!C1:C30,...)


"bigdaddy3" wrote:

Thanks for your input,but ive got 4 columns on each sheet would it be best to
use your formula but 4 times
--
BD3


"bpeltzer" wrote:

The average function will ignore blanks (that is, it won't count them in the
denominator, so you wind up with an average of the non-blank cells). You
probably just need something like
=average(Sheet1!A1:A30,Sheet2!A1:A30,Sheet3!A1:A30 ,Sheet4!A1:A30)

"bigdaddy3" wrote:

i need a formula to give me an average price of 4 columns of prices on 4
sheets (30 cells per column)where some cells can be blank,and even some
sheets blank,All cells are formatted as financial but no zeros showing.
-----Is it possible with a macro and if so formula please.
--
BD3

average price of 4 columns of figures but some cells can be bl
 

Thanks for that ill give it a go
--
BD3


"bpeltzer" wrote:

If the columns are contiguous, you could do something like
=average(Sheet1!A1:D30,Sheet2!A1:D30,Sheet3!A1:D30 ,Sheet4!A1:D30); if not
then you can call out each range within a single average function:
=average(Sheet1!A1:A30,Sheet1!C1:C30,...)


"bigdaddy3" wrote:

Thanks for your input,but ive got 4 columns on each sheet would it be best to
use your formula but 4 times
--
BD3


"bpeltzer" wrote:

The average function will ignore blanks (that is, it won't count them in the
denominator, so you wind up with an average of the non-blank cells). You
probably just need something like
=average(Sheet1!A1:A30,Sheet2!A1:A30,Sheet3!A1:A30 ,Sheet4!A1:A30)

"bigdaddy3" wrote:

i need a formula to give me an average price of 4 columns of prices on 4
sheets (30 cells per column)where some cells can be blank,and even some
sheets blank,All cells are formatted as financial but no zeros showing.
-----Is it possible with a macro and if so formula please.
--
BD3