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

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

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

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