Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non contiguous row
I was looking to do an average in non contigious rows using time but ignoring
zeros, so I used formula: =Average(if((D17,F17<0),(D17,F17),"")) but comes up #VALUE and when utilizing CTRL+SHIFT+ENTER value returned is same D17=0:09 (custom cell h:mm) and F17=0 (custom cell: h:mm), Is there a formula to complete this for non contigiuos row? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non contiguous row
First, these are non-contiguous columns not rows: second, are they alternate
D,F,H ,J etc? "Scott Kieta" wrote: I was looking to do an average in non contigious rows using time but ignoring zeros, so I used formula: =Average(if((D17,F17<0),(D17,F17),"")) but comes up #VALUE and when utilizing CTRL+SHIFT+ENTER value returned is same D17=0:09 (custom cell h:mm) and F17=0 (custom cell: h:mm), Is there a formula to complete this for non contigiuos row? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non contiguous row
You are right these are columns, and yes these are alternate columns. The
main goal is to make sure the formula omits those zero values when doing the average calculation. "Toppers" wrote: First, these are non-contiguous columns not rows: second, are they alternate D,F,H ,J etc? "Scott Kieta" wrote: I was looking to do an average in non contigious rows using time but ignoring zeros, so I used formula: =Average(if((D17,F17<0),(D17,F17),"")) but comes up #VALUE and when utilizing CTRL+SHIFT+ENTER value returned is same D17=0:09 (custom cell h:mm) and F17=0 (custom cell: h:mm), Is there a formula to complete this for non contigiuos row? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non contiguous row
try:
=AVERAGE(IF(MOD(COLUMN($D$3:$M$3),2)=0,IF($D$3:$M$ 30,$D$3:$M$3))) Change ranges to suit Enter with Ctrl+shift +Enter (array formula) "Scott Kieta" wrote: You are right these are columns, and yes these are alternate columns. The main goal is to make sure the formula omits those zero values when doing the average calculation. "Toppers" wrote: First, these are non-contiguous columns not rows: second, are they alternate D,F,H ,J etc? "Scott Kieta" wrote: I was looking to do an average in non contigious rows using time but ignoring zeros, so I used formula: =Average(if((D17,F17<0),(D17,F17),"")) but comes up #VALUE and when utilizing CTRL+SHIFT+ENTER value returned is same D17=0:09 (custom cell h:mm) and F17=0 (custom cell: h:mm), Is there a formula to complete this for non contigiuos row? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non contiguous row
This works, excellent, thank you for your help. I have never used the MOD or
COLUMN functions before, if you don't mind can you give a short response on those uses? Thank You "Toppers" wrote: try: =AVERAGE(IF(MOD(COLUMN($D$3:$M$3),2)=0,IF($D$3:$M$ 30,$D$3:$M$3))) Change ranges to suit Enter with Ctrl+shift +Enter (array formula) "Scott Kieta" wrote: You are right these are columns, and yes these are alternate columns. The main goal is to make sure the formula omits those zero values when doing the average calculation. "Toppers" wrote: First, these are non-contiguous columns not rows: second, are they alternate D,F,H ,J etc? "Scott Kieta" wrote: I was looking to do an average in non contigious rows using time but ignoring zeros, so I used formula: =Average(if((D17,F17<0),(D17,F17),"")) but comes up #VALUE and when utilizing CTRL+SHIFT+ENTER value returned is same D17=0:09 (custom cell h:mm) and F17=0 (custom cell: h:mm), Is there a formula to complete this for non contigiuos row? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average non contiguous row
COLUMN returns the column number so column D=4, E=5 etc.
The MOD function returns a remainder after the division by the divisor, in this case 2. So all EVEN colums will have a remainder of 0 (if we divide by 2) whereas ODD columns will have a remainder of 1. HTH "Scott Kieta" wrote: This works, excellent, thank you for your help. I have never used the MOD or COLUMN functions before, if you don't mind can you give a short response on those uses? Thank You "Toppers" wrote: try: =AVERAGE(IF(MOD(COLUMN($D$3:$M$3),2)=0,IF($D$3:$M$ 30,$D$3:$M$3))) Change ranges to suit Enter with Ctrl+shift +Enter (array formula) "Scott Kieta" wrote: You are right these are columns, and yes these are alternate columns. The main goal is to make sure the formula omits those zero values when doing the average calculation. "Toppers" wrote: First, these are non-contiguous columns not rows: second, are they alternate D,F,H ,J etc? "Scott Kieta" wrote: I was looking to do an average in non contigious rows using time but ignoring zeros, so I used formula: =Average(if((D17,F17<0),(D17,F17),"")) but comes up #VALUE and when utilizing CTRL+SHIFT+ENTER value returned is same D17=0:09 (custom cell h:mm) and F17=0 (custom cell: h:mm), Is there a formula to complete this for non contigiuos row? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Contiguous Line Count | Excel Discussion (Misc queries) | |||
average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions | |||
contiguous reference | Excel Discussion (Misc queries) | |||
SUMIF non-contiguous range | Excel Worksheet Functions | |||
Average Non-Contiguous numbers | Excel Worksheet Functions |