Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
(sorry about the double post....FNG)
anyway, i am trying to average columns using only the data that falls 0<X1 (between 0 and 1). So if i am averaging cols A:C, sometimes 0A and sometimes 0C and I want to average only those values in A:C that at that particular time are 0<X1. have tried the following so far: {=AVERAGE(IF(and(a2:c20,a2:c2<1,a2:c2,""))}, and {=AVERAGE(IF(0<a2:c21,a2:c2,""))} but to no avail :( help would be greatly appreciated! thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
I bet you need this
=IF(AND(A2:C20,A2:C2<1),AVERAGE(A2:C2),"") (press control + shift + enter to enter it and get the curly brackets) -- Allllen "neversummer" wrote: (sorry about the double post....FNG) anyway, i am trying to average columns using only the data that falls 0<X1 (between 0 and 1). So if i am averaging cols A:C, sometimes 0A and sometimes 0C and I want to average only those values in A:C that at that particular time are 0<X1. have tried the following so far: {=AVERAGE(IF(and(a2:c20,a2:c2<1,a2:c2,""))}, and {=AVERAGE(IF(0<a2:c21,a2:c2,""))} but to no avail :( help would be greatly appreciated! thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
I believe I have your answer:
Let me use 1 (one) column of numbers as follows: A B C D 0.8 = -- AND(A10,A1<1) = IF(AND(A10,A1<1),1,0) 1.5 = -- AND(A20,A2<1) = IF(AND(A20,A2<1),1,0) -15.0 = -- AND(A30,A3<1) = IF(AND(A30,A3<1),1,0) 0.7 = -- AND(A40,A4<1) = IF(AND(A40,A4<1),1,0) = SUMPRODUCT(A1:A4,B1:B4) = SUM(C1:C4) =B6/C6 All the above...COPY AND PASTE...The B6/C6 (in cell D6) is your AVERAGE!!! I hope this helps; pls. let me know. Thanks, FLKulchar "neversummer" wrote: (sorry about the double post....FNG) anyway, i am trying to average columns using only the data that falls 0<X1 (between 0 and 1). So if i am averaging cols A:C, sometimes 0A and sometimes 0C and I want to average only those values in A:C that at that particular time are 0<X1. have tried the following so far: {=AVERAGE(IF(and(a2:c20,a2:c2<1,a2:c2,""))}, and {=AVERAGE(IF(0<a2:c21,a2:c2,""))} but to no avail :( help would be greatly appreciated! thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there any way to get an AVERAGEIF & MEDIANIF function in Exce | Excel Worksheet Functions | |||
averageif | Excel Discussion (Misc queries) | |||
how can i calc averageif | Excel Worksheet Functions | |||
"AverageIF" alternative | Excel Discussion (Misc queries) | |||
AVERAGEIF Function out there? | Excel Discussion (Misc queries) |