Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging values in column A when certain criteria are met in colu
I have values in column Y, and a number of different names in column C. I
would like to create an average of the Y values for a select group of the names from C. I have tried the following two ways and get a #DIV/0! error both times. Any help is appreciated! {=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))} {=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging values in column A when certain criteria are met in colu
Try
(with blank entries in Y for mathcing records) =AVERAGE(IF($C$2:$C$60000={"Jane","John","Joan","J ean"},$Y$2:$Y$60000)) (excluding blanks) =AVERAGE(IF(($C$2:$C$60000={"Jane","John","Joan"," Jean"})*($I$2:$I$60000<""),$Y$2:$Y$60000)) If this post helps click Yes --------------- Jacob Skaria "VichyB" wrote: I have values in column Y, and a number of different names in column C. I would like to create an average of the Y values for a select group of the names from C. I have tried the following two ways and get a #DIV/0! error both times. Any help is appreciated! {=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))} {=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))} |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging values in column A when certain criteria are met in colu
Please note that these are array formulas. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "VichyB" wrote: I have values in column Y, and a number of different names in column C. I would like to create an average of the Y values for a select group of the names from C. I have tried the following two ways and get a #DIV/0! error both times. Any help is appreciated! {=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))} {=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))} |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging values in column A when certain criteria are met in
that did it! thanks so much.
"Jacob Skaria" wrote: Try (with blank entries in Y for mathcing records) =AVERAGE(IF($C$2:$C$60000={"Jane","John","Joan","J ean"},$Y$2:$Y$60000)) (excluding blanks) =AVERAGE(IF(($C$2:$C$60000={"Jane","John","Joan"," Jean"})*($I$2:$I$60000<""),$Y$2:$Y$60000)) If this post helps click Yes --------------- Jacob Skaria "VichyB" wrote: I have values in column Y, and a number of different names in column C. I would like to create an average of the Y values for a select group of the names from C. I have tried the following two ways and get a #DIV/0! error both times. Any help is appreciated! {=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))} {=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count how many criteria in a column match criteria in another colu | Excel Discussion (Misc queries) | |||
Sum of items in a column if they meet two criteria in another colu | Excel Worksheet Functions | |||
i wish to lookup values in column A, & add adjacent values in colu | Excel Discussion (Misc queries) | |||
HELP: Averaging values if they meet certain criteria | Excel Worksheet Functions | |||
Advanced Filter for Values in Column M greater than Values in Colu | Excel Discussion (Misc queries) |