software

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

Thanks for your input,but ive got 4 columns on each sheet would it be best to
use your formula but 4 times
--
BD3quot;bpeltzerquot; wrote:

gt; The average function will ignore blanks (that is, it won't count them in the
gt; denominator, so you wind up with an average of the non-blank cells). You
gt; probably just need something like
gt; =average(Sheet1!A1:A30,Sheet2!A1:A30,Sheet3!A1:A30 ,Sheet4!A1:A30)
gt;
gt; quot;bigdaddy3quot; wrote:
gt;
gt; gt; i need a formula to give me an average price of 4 columns of prices on 4
gt; gt; sheets (30 cells per column)where some cells can be blank,and even some
gt; gt; sheets blank,All cells are formatted as financial but no zeros showing.
gt; gt; -----Is it possible with a macro and if so formula please.
gt; gt; --
gt; gt; BD3

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

gt; Thanks for your input,but ive got 4 columns on each sheet would it be best to
gt; use your formula but 4 times
gt; --
gt; BD3
gt;
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; The average function will ignore blanks (that is, it won't count them in the
gt; gt; denominator, so you wind up with an average of the non-blank cells). You
gt; gt; probably just need something like
gt; gt; =average(Sheet1!A1:A30,Sheet2!A1:A30,Sheet3!A1:A30 ,Sheet4!A1:A30)
gt; gt;
gt; gt; quot;bigdaddy3quot; wrote:
gt; gt;
gt; gt; gt; i need a formula to give me an average price of 4 columns of prices on 4
gt; gt; gt; sheets (30 cells per column)where some cells can be blank,and even some
gt; gt; gt; sheets blank,All cells are formatted as financial but no zeros showing.
gt; gt; gt; -----Is it possible with a macro and if so formula please.
gt; gt; gt; --
gt; gt; gt; BD3

Thanks for that ill give it a go
--
BD3quot;bpeltzerquot; wrote:

gt; If the columns are contiguous, you could do something like
gt; =average(Sheet1!A130,Sheet2!A130,Sheet3!A130 ,Sheet4!A130); if not
gt; then you can call out each range within a single average function:
gt; =average(Sheet1!A1:A30,Sheet1!C1:C30,...)
gt;
gt;
gt; quot;bigdaddy3quot; wrote:
gt;
gt; gt; Thanks for your input,but ive got 4 columns on each sheet would it be best to
gt; gt; use your formula but 4 times
gt; gt; --
gt; gt; BD3
gt; gt;
gt; gt;
gt; gt; quot;bpeltzerquot; wrote:
gt; gt;
gt; gt; gt; The average function will ignore blanks (that is, it won't count them in the
gt; gt; gt; denominator, so you wind up with an average of the non-blank cells). You
gt; gt; gt; probably just need something like
gt; gt; gt; =average(Sheet1!A1:A30,Sheet2!A1:A30,Sheet3!A1:A30 ,Sheet4!A1:A30)
gt; gt; gt;
gt; gt; gt; quot;bigdaddy3quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; i need a formula to give me an average price of 4 columns of prices on 4
gt; gt; gt; gt; sheets (30 cells per column)where some cells can be blank,and even some
gt; gt; gt; gt; sheets blank,All cells are formatted as financial but no zeros showing.
gt; gt; gt; gt; -----Is it possible with a macro and if so formula please.
gt; gt; gt; gt; --
gt; gt; gt; gt; BD3