Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
I assume I use a lookup function, but not sure. I would define the range
with a name, I know that much, but the formula stumps me. I have a list that could read something like this: JAN Con-Way 2 JAN SFS 3 APR SFS 3 JUN SFS 3 JUL TransX 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 SEP TransX 5 FEB Yellow 5 FEB Yellow 5 In another cell, for example, I would like to put a formula that would look up all of "Yellow" in this list and then give me the average of the numbers for Yellow. I think it's simple enough but I'm not versed enough! Connie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
SUMIF will total cells when an associated cell meets a certain condition;
COUNTIF will count such cells. So your average would be =sumif(b:b,"Yellow",c:c)/countif(b:b,"Yellow"). Note that "Yellow" could just as well be another cell reference. "Connie Martin" wrote: I assume I use a lookup function, but not sure. I would define the range with a name, I know that much, but the formula stumps me. I have a list that could read something like this: JAN Con-Way 2 JAN SFS 3 APR SFS 3 JUN SFS 3 JUL TransX 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 SEP TransX 5 FEB Yellow 5 FEB Yellow 5 In another cell, for example, I would like to put a formula that would look up all of "Yellow" in this list and then give me the average of the numbers for Yellow. I think it's simple enough but I'm not versed enough! Connie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
This is an arrya formula, one you enter with Shift+Ctrl+Enter
=AVERAGE(IF(B2:B13="Yellow",C2:C13)) where your data starts in row 2 "Connie Martin" wrote: I assume I use a lookup function, but not sure. I would define the range with a name, I know that much, but the formula stumps me. I have a list that could read something like this: JAN Con-Way 2 JAN SFS 3 APR SFS 3 JUN SFS 3 JUL TransX 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 SEP TransX 5 FEB Yellow 5 FEB Yellow 5 In another cell, for example, I would like to put a formula that would look up all of "Yellow" in this list and then give me the average of the numbers for Yellow. I think it's simple enough but I'm not versed enough! Connie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Thank you, this works! Another question, if you don't mind. Using the
sample columns below, I need a formula that looks for Yellow and counts the number of times it was 7 or less from column C. In looking at the little table below, the answer would be 9. for SFS it would be 2, for FFE it would be 1. They will be all separate formulas, of course. I only need one example---for Yellow. Yellow 5 Yellow 6 SFS 3 Yellow 6 Yellow 6 Yellow 6 Yellow 6 Yellow 7 Yellow 7 Yellow 7 Yellow 8 FFE 7 FFE 11 FFE 11 SFS 3 "bpeltzer" wrote: SUMIF will total cells when an associated cell meets a certain condition; COUNTIF will count such cells. So your average would be =sumif(b:b,"Yellow",c:c)/countif(b:b,"Yellow"). Note that "Yellow" could just as well be another cell reference. "Connie Martin" wrote: I assume I use a lookup function, but not sure. I would define the range with a name, I know that much, but the formula stumps me. I have a list that could read something like this: JAN Con-Way 2 JAN SFS 3 APR SFS 3 JUN SFS 3 JUL TransX 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 SEP TransX 5 FEB Yellow 5 FEB Yellow 5 In another cell, for example, I would like to put a formula that would look up all of "Yellow" in this list and then give me the average of the numbers for Yellow. I think it's simple enough but I'm not versed enough! Connie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
This formula works when the columns are sorted a certain way, but when I sort
them differently, the formula yields 0. My data starts in row 5 and I adjusted the formula accordingly, but for some reason it doesn't always work. I must be doing something wrong. Although I don't understand bpeltzer's reponse, the formula works and is consistent no matter how I sort the columns. Thank you for responding. Connie "Duke Carey" wrote: This is an arrya formula, one you enter with Shift+Ctrl+Enter =AVERAGE(IF(B2:B13="Yellow",C2:C13)) where your data starts in row 2 "Connie Martin" wrote: I assume I use a lookup function, but not sure. I would define the range with a name, I know that much, but the formula stumps me. I have a list that could read something like this: JAN Con-Way 2 JAN SFS 3 APR SFS 3 JUN SFS 3 JUL TransX 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 JAN Yellow 4 SEP TransX 5 FEB Yellow 5 FEB Yellow 5 In another cell, for example, I would like to put a formula that would look up all of "Yellow" in this list and then give me the average of the numbers for Yellow. I think it's simple enough but I'm not versed enough! Connie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
If the data starts in row 5, and the Yellow text is in column A, the values
in column B, use =SUMPRODUCT(--(A5:A25="Yellow"),--(B5:B25<=7)) "Connie Martin" wrote: Thank you, this works! Another question, if you don't mind. Using the sample columns below, I need a formula that looks for Yellow and counts the number of times it was 7 or less from column C. In looking at the little table below, the answer would be 9. for SFS it would be 2, for FFE it would be 1. They will be all separate formulas, of course. I only need one example---for Yellow. Yellow 5 Yellow 6 SFS 3 Yellow 6 Yellow 6 Yellow 6 Yellow 6 Yellow 7 Yellow 7 Yellow 7 Yellow 8 FFE 7 FFE 11 FFE 11 SFS 3 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Thank you, Duke. That works great! What a great resource this newsgroup is!
Wow! I'm impressed. Thanks to both of you! Connie "Duke Carey" wrote: If the data starts in row 5, and the Yellow text is in column A, the values in column B, use =SUMPRODUCT(--(A5:A25="Yellow"),--(B5:B25<=7)) "Connie Martin" wrote: Thank you, this works! Another question, if you don't mind. Using the sample columns below, I need a formula that looks for Yellow and counts the number of times it was 7 or less from column C. In looking at the little table below, the answer would be 9. for SFS it would be 2, for FFE it would be 1. They will be all separate formulas, of course. I only need one example---for Yellow. Yellow 5 Yellow 6 SFS 3 Yellow 6 Yellow 6 Yellow 6 Yellow 6 Yellow 7 Yellow 7 Yellow 7 Yellow 8 FFE 7 FFE 11 FFE 11 SFS 3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |