Averaging selected values

[Guest]

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

 

[Biff]

Hi!

Not a lot of info to go by:

Entered with the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(A1:A10>10,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

 

[Guest]

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]

Hi!

Try these entered as array's with the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(A1:A100="home",D1100))

=AVERAGE(IF(A1:A100="away",D1100))

Biff