Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Averaging selected values
Hello everyone
I have the following two queries: 1. I want to average values that meet a specific condition i.e. I know how to use an IF statement but I don't know how to average those values that meet the IF statement. 2. I want to lookup along a single row with no other data and take the value to the right of the word I am searching. I know hlookup doesn't work because it will only associate with fixed parameters. In my case I want to always take the cell to the right of another cell in a single row and this cell might be changing in the spreadsheet. Thanks in advance |
#2
|
|||
|
|||
Hi!
Not a lot of info to go by: Entered with the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A1010,A1:A10)) Assume A1 = word to search for in the range D1:H1 =INDEX(D1:H1,MATCH(A1,D1:H1,0)+1) Will return the value in the next column to the right of the found lookup value. If the lookup value is in the last cell in the range then the formula will return an error. Biff "Hellion" wrote in message ... Hello everyone I have the following two queries: 1. I want to average values that meet a specific condition i.e. I know how to use an IF statement but I don't know how to average those values that meet the IF statement. 2. I want to lookup along a single row with no other data and take the value to the right of the word I am searching. I know hlookup doesn't work because it will only associate with fixed parameters. In my case I want to always take the cell to the right of another cell in a single row and this cell might be changing in the spreadsheet. Thanks in advance |
#3
|
|||
|
|||
The answer to the second question works and thanks for that. In terms of the
first question let me make it more clear. We have a column with games stating home or away. There is another column stating the number of goals scored. I want to average those values that relate to home games and only those values that relate to the away games. Assume column with home/away is column A and column with goals is column D. Thanks "Biff" wrote: Hi! Not a lot of info to go by: Entered with the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A1010,A1:A10)) Assume A1 = word to search for in the range D1:H1 =INDEX(D1:H1,MATCH(A1,D1:H1,0)+1) Will return the value in the next column to the right of the found lookup value. If the lookup value is in the last cell in the range then the formula will return an error. Biff "Hellion" wrote in message ... Hello everyone I have the following two queries: 1. I want to average values that meet a specific condition i.e. I know how to use an IF statement but I don't know how to average those values that meet the IF statement. 2. I want to lookup along a single row with no other data and take the value to the right of the word I am searching. I know hlookup doesn't work because it will only associate with fixed parameters. In my case I want to always take the cell to the right of another cell in a single row and this cell might be changing in the spreadsheet. Thanks in advance |
#4
|
|||
|
|||
Hi!
Try these entered as array's with the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A100="home",D1:D100)) =AVERAGE(IF(A1:A100="away",D1:D100)) Biff "Hellion" wrote in message ... The answer to the second question works and thanks for that. In terms of the first question let me make it more clear. We have a column with games stating home or away. There is another column stating the number of goals scored. I want to average those values that relate to home games and only those values that relate to the away games. Assume column with home/away is column A and column with goals is column D. Thanks "Biff" wrote: Hi! Not a lot of info to go by: Entered with the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A1010,A1:A10)) Assume A1 = word to search for in the range D1:H1 =INDEX(D1:H1,MATCH(A1,D1:H1,0)+1) Will return the value in the next column to the right of the found lookup value. If the lookup value is in the last cell in the range then the formula will return an error. Biff "Hellion" wrote in message ... Hello everyone I have the following two queries: 1. I want to average values that meet a specific condition i.e. I know how to use an IF statement but I don't know how to average those values that meet the IF statement. 2. I want to lookup along a single row with no other data and take the value to the right of the word I am searching. I know hlookup doesn't work because it will only associate with fixed parameters. In my case I want to always take the cell to the right of another cell in a single row and this cell might be changing in the spreadsheet. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically clear values from a range of selected cells | Excel Discussion (Misc queries) | |||
Perform oiperations relative to initial selected cell | Excel Discussion (Misc queries) | |||
Passing selected workbook name and values to a macro | Excel Worksheet Functions | |||
averaging from one selected cell to another | Excel Discussion (Misc queries) | |||
averaging from one selected cell to another | Excel Discussion (Misc queries) |