Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average formula ingoring empty cells
I need to create a formula to average several cells, but the cells are not in a range. So far I have =AVERAGE(G3, J3, M3, P3, S3), which works fine, but cells M3, P3 and S3 are empty (zero) until I have the data that I need. I want the formula to already be set up for future input. I've been able to make it work for a range of values (G3:S3), but not with indivual cells in a group. Help please! Thanks. :) -- jenparker1234 ------------------------------------------------------------------------ jenparker1234's Profile: http://www.excelforum.com/member.php...o&userid=36266 View this thread: http://www.excelforum.com/showthread...hreadid=560539 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average formula ingoring empty cells
the average formula you posted will ignore blanks, but not zero values. here is an alternative formula, which will average the cells you requested, including only the non zero values. J is column 10, M is column 13, etc. =SUMPRODUCT((MOD(COLUMN(G3:S3)-1,3)=0)*(G3:S3<0)*(G3:S3))/SUMPRODUCT((MOD(COLUMN(G3:S3)-1,3)=0)*(G3:S3<0)) -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=560539 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average formula ingoring empty cells
Hi!
Not sure what you're asking for. cells M3, P3 and S3 are empty (zero) Are the cells EMPTY or do they contain the number 0? AVERAGE will ignore the empty cells, they are not included as 0 values. Do you want to exclude cells that contain the number 0? Biff "jenparker1234" wrote in message news:jenparker1234.2atbnk_1152674436.2855@excelfor um-nospam.com... I need to create a formula to average several cells, but the cells are not in a range. So far I have =AVERAGE(G3, J3, M3, P3, S3), which works fine, but cells M3, P3 and S3 are empty (zero) until I have the data that I need. I want the formula to already be set up for future input. I've been able to make it work for a range of values (G3:S3), but not with indivual cells in a group. Help please! Thanks. :) -- jenparker1234 ------------------------------------------------------------------------ jenparker1234's Profile: http://www.excelforum.com/member.php...o&userid=36266 View this thread: http://www.excelforum.com/showthread...hreadid=560539 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average formula ingoring empty cells
another solution to ignore the zero values (or blanks) in the range you requested - enter this with control+shift+enter =AVERAGE(IF(MOD(COLUMN($G$3:$S$3)-1,3)=0,IF($G$3:$S$30,$G$3:$S$3))) -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=560539 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions | |||
Empty Cells, Spaces, Cond Format? | Excel Discussion (Misc queries) | |||
excluding cells from the formula when empty | Excel Worksheet Functions |