Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average function
I would like to average column C7 to C37 but would like to exclude
cells with just zeros in them. Ex. AVERAGE(C7:C37) Each cell from C7 to C37 has a Paste Link to it from another worksheet so zeros appear in all the cells that have not yet been entered. I get an incorrect average because its dividing by 31 rather than cells numbers otherr than zero that have been entered. Thanks, Rube |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average function
Try this:
=SUM(C7:C37)/COUNTIF(C7:C37,"<0") OR...this ARRAY FORMULA =AVERAGE(IF(C7:C37<0,C7:C37)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "Rubix" wrote: I would like to average column C7 to C37 but would like to exclude cells with just zeros in them. Ex. AVERAGE(C7:C37) Each cell from C7 to C37 has a Paste Link to it from another worksheet so zeros appear in all the cells that have not yet been entered. I get an incorrect average because its dividing by 31 rather than cells numbers otherr than zero that have been entered. Thanks, Rube |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average function
On Thu, 4 Jan 2007 18:45:00 -0800, Ron Coderre
wrote: Tried it. I got an Value error. Try this: =SUM(C7:C37)/COUNTIF(C7:C37,"<0") OR...this ARRAY FORMULA =AVERAGE(IF(C7:C37<0,C7:C37)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "Rubix" wrote: I would like to average column C7 to C37 but would like to exclude cells with just zeros in them. Ex. AVERAGE(C7:C37) Each cell from C7 to C37 has a Paste Link to it from another worksheet so zeros appear in all the cells that have not yet been entered. I get an incorrect average because its dividing by 31 rather than cells numbers otherr than zero that have been entered. Thanks, Rube |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average function
Regarding:
Tried it. I got an Value error. Sometimes less is more....but, in this case less is definitely not enough. I tested both formulas with the C7:C37 range containing numerics (pos, neg, zero), text, and blanks without incident. As long as the formulas are constructed properly the problem lies in the contents of C7:C37. Most likely there is an error value in the range. Post back if you have more information or questions. *********** Regards, Ron XL2002, WinXP "Rubix" wrote: On Thu, 4 Jan 2007 18:45:00 -0800, Ron Coderre wrote: Tried it. I got an Value error. Try this: =SUM(C7:C37)/COUNTIF(C7:C37,"<0") OR...this ARRAY FORMULA =AVERAGE(IF(C7:C37<0,C7:C37)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "Rubix" wrote: I would like to average column C7 to C37 but would like to exclude cells with just zeros in them. Ex. AVERAGE(C7:C37) Each cell from C7 to C37 has a Paste Link to it from another worksheet so zeros appear in all the cells that have not yet been entered. I get an incorrect average because its dividing by 31 rather than cells numbers otherr than zero that have been entered. Thanks, Rube |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average function
I tried your SUM function formula and it worked. Sorry I should have
explained that I was getting a Value error with the Average function. Not sure why if it works for you. Thank you for your help. On Fri, 5 Jan 2007 04:49:01 -0800, Ron Coderre wrote: Regarding: Tried it. I got an Value error. Sometimes less is more....but, in this case less is definitely not enough. I tested both formulas with the C7:C37 range containing numerics (pos, neg, zero), text, and blanks without incident. As long as the formulas are constructed properly the problem lies in the contents of C7:C37. Most likely there is an error value in the range. Post back if you have more information or questions. *********** Regards, Ron XL2002, WinXP "Rubix" wrote: On Thu, 4 Jan 2007 18:45:00 -0800, Ron Coderre wrote: Tried it. I got an Value error. Try this: =SUM(C7:C37)/COUNTIF(C7:C37,"<0") OR...this ARRAY FORMULA =AVERAGE(IF(C7:C37<0,C7:C37)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "Rubix" wrote: I would like to average column C7 to C37 but would like to exclude cells with just zeros in them. Ex. AVERAGE(C7:C37) Each cell from C7 to C37 has a Paste Link to it from another worksheet so zeros appear in all the cells that have not yet been entered. I get an incorrect average because its dividing by 31 rather than cells numbers otherr than zero that have been entered. Thanks, Rube |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Using text for the range in AVERAGE function | Excel Worksheet Functions | |||
Figuring daily average...function ??? | Excel Worksheet Functions | |||
Average function assistance | Excel Discussion (Misc queries) |