Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non-adjacent cells & exclude zeros
I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it. Example: =AVERAGE(H12,V12,AJ12) How can I rewrite this so that the formula will not include the cell in the average if its value is zero? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non-adjacent cells & exclude zeros
One way
=AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12)) needs to be entered with ctrl + shift & enter this assumes you have the same number of columns between each value like you have in your example -- Regards, Peo Sjoblom "Danni2004" wrote in message ... I want to find an average of three cells that are non-adjacent and will occasionally have a zero in it. Example: =AVERAGE(H12,V12,AJ12) How can I rewrite this so that the formula will not include the cell in the average if its value is zero? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non-adjacent cells & exclude zeros
Thanks Peo.
Can you clarify what the "8" is for in this formula? Also, I've seen other posts with the Ctrl + Shift & Enter before. Can you explain why you have to use this? "Peo Sjoblom" wrote: One way =AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12)) needs to be entered with ctrl + shift & enter this assumes you have the same number of columns between each value like you have in your example -- Regards, Peo Sjoblom "Danni2004" wrote in message ... I want to find an average of three cells that are non-adjacent and will occasionally have a zero in it. Example: =AVERAGE(H12,V12,AJ12) How can I rewrite this so that the formula will not include the cell in the average if its value is zero? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non-adjacent cells & exclude zeros
What are the column numbers for H12, V12, and AJ12? What does
MOD(column_number,14) return in each of those cases? Control Shift Enter is used to enter an array formula. look up array formula in Excel help. -- David Biddulph "Danni2004" wrote in message ... Thanks Peo. Can you clarify what the "8" is for in this formula? Also, I've seen other posts with the Ctrl + Shift & Enter before. Can you explain why you have to use this? "Peo Sjoblom" wrote: One way =AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12)) needs to be entered with ctrl + shift & enter this assumes you have the same number of columns between each value like you have in your example "Danni2004" wrote in message ... I want to find an average of three cells that are non-adjacent and will occasionally have a zero in it. Example: =AVERAGE(H12,V12,AJ12) How can I rewrite this so that the formula will not include the cell in the average if its value is zero? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Non-Adjacent Cells in Budget Spreadsheet | Excel Worksheet Functions | |||
Average Non-Adjacent Cells in Budget Spreadsheet | Excel Worksheet Functions | |||
Another Average non-adjacent cells question... | Excel Discussion (Misc queries) | |||
Average non-adjacent cells if the cell does not contain zero | Excel Discussion (Misc queries) | |||
Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions |