Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
My data is in columns. I want to average those values that are 0<X1 only.
Tried using Average(if()) but am so far unsuccessful. Suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
Try this: (array entered)
=AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2)) Biff "neversummer" wrote in message ... My data is in columns. I want to average those values that are 0<X1 only. Tried using Average(if()) but am so far unsuccessful. Suggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
that worked like a charm! thanks a ton. if i may....
what does the * do in this situation, is it like an either/or, so if i got crazy and wanted to continue with the conditions could i add another * ? cheers "Biff" wrote: Try this: (array entered) =AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2)) Biff "neversummer" wrote in message ... My data is in columns. I want to average those values that are 0<X1 only. Tried using Average(if()) but am so far unsuccessful. Suggestions? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
anyway to do this if the data to be averaged is not in adjacent columns??
"neversummer" wrote: that worked like a charm! thanks a ton. if i may.... what does the * do in this situation, is it like an either/or, so if i got crazy and wanted to continue with the conditions could i add another * ? cheers "Biff" wrote: Try this: (array entered) =AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2)) Biff "neversummer" wrote in message ... My data is in columns. I want to average those values that are 0<X1 only. Tried using Average(if()) but am so far unsuccessful. Suggestions? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
It multiplies the TRUE/FALSE arrays returned from the conditional tests to
give an array of 1/0 which is used to determine whether the cells in the range are included in the AVERAGE. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "neversummer" wrote in message ... that worked like a charm! thanks a ton. if i may.... what does the * do in this situation, is it like an either/or, so if i got crazy and wanted to continue with the conditions could i add another * ? cheers "Biff" wrote: Try this: (array entered) =AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2)) Biff "neversummer" wrote in message ... My data is in columns. I want to average those values that are 0<X1 only. Tried using Average(if()) but am so far unsuccessful. Suggestions? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
average(if())
Do you mean, A, C and E say (that is regularly spaced),
=SUMPRODUCT(--(MOD(COLUMN(A2:E2),2)=1),--(A2:E20),--(A2:E2<1),A2:E2)/SUMPRO DUCT(--(MOD(COLUMN(A2:E2),2)=1),--(A2:E20),--(A2:E2<1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "neversummer" wrote in message ... anyway to do this if the data to be averaged is not in adjacent columns?? "neversummer" wrote: that worked like a charm! thanks a ton. if i may.... what does the * do in this situation, is it like an either/or, so if i got crazy and wanted to continue with the conditions could i add another * ? cheers "Biff" wrote: Try this: (array entered) =AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2)) Biff "neversummer" wrote in message ... My data is in columns. I want to average those values that are 0<X1 only. Tried using Average(if()) but am so far unsuccessful. Suggestions? |
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) |