Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula to delete blank cells across multiple columns? | Excel Worksheet Functions | |||
Value between 2 dates | Excel Worksheet Functions | |||
Counting blank and filled cells within a range. | Excel Discussion (Misc queries) | |||
Copy down - special to fill only the blank cells | Excel Discussion (Misc queries) | |||
blank cells | Excel Discussion (Misc queries) |