Please help

I want to work an average percent from a list of percents in column C row
1-1000 where the value of column B is equal to "Wales". The frequency of
Wales is too large for to manually select the cells so how can I employ the
Daverage function to calculate this,

Thanks

Without DAverage

=AVERAGE(IF(B1:B1000="Wales",C1:C1000))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nir020" wrote in message
...
Please help

I want to work an average percent from a list of percents in column C row
1-1000 where the value of column B is equal to "Wales". The frequency of
Wales is too large for to manually select the cells so how can I employ
the
Daverage function to calculate this,

Thanks

Hi

An answer from Wales!!!

Try the array formula
{=AVERAGE(IF($A$1:$A$1000="Wales",B2:B101))}
Commit with Ctrl+Shift+Enter and Excel will include the curly braces { }
for you. Do not type them yourself.

Or insert a header row with Country, and Value
Apply DataFilterAutofilter and Select Wales for Column A and use the formula
=SUBTOTAL(1,B2:B1001)

Regards

Roger Govier


nir020 wrote:
Please help

I want to work an average percent from a list of percents in column C row
1-1000 where the value of column B is equal to "Wales". The frequency of
Wales is too large for to manually select the cells so how can I employ the
Daverage function to calculate this,

Thanks

Why do you need the curley brackets?

"Roger Govier" wrote:

Hi

An answer from Wales!!!

Try the array formula
{=AVERAGE(IF($A$1:$A$1000="Wales",B2:B101))}
Commit with Ctrl+Shift+Enter and Excel will include the curly braces { }
for you. Do not type them yourself.

Or insert a header row with Country, and Value
Apply DataFilterAutofilter and Select Wales for Column A and use the formula
=SUBTOTAL(1,B2:B1001)

Regards

Roger Govier


nir020 wrote:
Please help

I want to work an average percent from a list of percents in column C row
1-1000 where the value of column B is equal to "Wales". The frequency of
Wales is too large for to manually select the cells so how can I employ the
Daverage function to calculate this,

Thanks

Hi

It is Excel's way of determining that it is an array formula.
You must not type the brackets yourself, they are automatically entered when
you use Ctrl+Shift+Enter to create the formula, or whenever you edit the
formula.

Note, you will obtain a result as a non-array entered formula, but it will
not be the correct result.

Regards

Roger Govier


nir020 wrote:
Why do you need the curley brackets?

"Roger Govier" wrote:


Hi

An answer from Wales!!!

Try the array formula
{=AVERAGE(IF($A$1:$A$1000="Wales",B2:B101))}
Commit with Ctrl+Shift+Enter and Excel will include the curly braces { }
for you. Do not type them yourself.

Or insert a header row with Country, and Value
Apply DataFilterAutofilter and Select Wales for Column A and use the formula
=SUBTOTAL(1,B2:B1001)

Regards

Roger Govier


nir020 wrote:

Please help

I want to work an average percent from a list of percents in column C row
1-1000 where the value of column B is equal to "Wales". The frequency of
Wales is too large for to manually select the cells so how can I employ the
Daverage function to calculate this,

Thanks

Be aware that this will probably give a wrong answer as it uses the amount
in B2 when A1 equals Wales. For some reason, Roger shifted the second range
down by a row.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Roger Govier" wrote in message
...
Hi

An answer from Wales!!!

Try the array formula
{=AVERAGE(IF($A$1:$A$1000="Wales",B2:B101))}
Commit with Ctrl+Shift+Enter and Excel will include the curly braces
{ }
for you. Do not type them yourself.

Or insert a header row with Country, and Value
Apply DataFilterAutofilter and Select Wales for Column A and use the
formula
=SUBTOTAL(1,B2:B1001)

Regards

Roger Govier


nir020 wrote:
Please help

I want to work an average percent from a list of percents in column C
row
1-1000 where the value of column B is equal to "Wales". The frequency of
Wales is too large for to manually select the cells so how can I employ
the
Daverage function to calculate this,

Thanks

Oops!!

Thank you Bob.
Because I had inserted a row to use the Subtotal function, I copied the
wrong range to my answer. You are quite right it should have been
the array formula
{=AVERAGE(IF($A$1:$A$1000="Wales",$B$1:$B$1000))}

Lucky the English are wide awake!!!

Regards

Roger Govier


Bob Phillips wrote:
Be aware that this will probably give a wrong answer as it uses the amount
in B2 when A1 equals Wales. For some reason, Roger shifted the second range
down by a row.

Hey, I may be English, but my ancestry is Welsh, as you can tell from my
name, whereas as I understand it, Govier is an English name from Devon,
associated with the accurse4d Normans!

Bob


"Roger Govier" wrote in message
...
Oops!!

Thank you Bob.
Because I had inserted a row to use the Subtotal function, I copied the
wrong range to my answer. You are quite right it should have been
the array formula
{=AVERAGE(IF($A$1:$A$1000="Wales",$B$1:$B$1000))}

Lucky the English are wide awake!!!

Regards

Roger Govier


Bob Phillips wrote:
Be aware that this will probably give a wrong answer as it uses the
amount
in B2 when A1 equals Wales. For some reason, Roger shifted the second
range
down by a row.

True, but the other 3 grandparents were Welsh and I was born in Wales.
Name traced back to 1596 at Swanage - not too far from you. My guess is the
ancestors were were drunken Norman fishermen, turned the wrong way and
washed up in Dorset!!!

Regards

Roger Govier


Bob Phillips wrote:
Hey, I may be English, but my ancestry is Welsh, as you can tell from my
name, whereas as I understand it, Govier is an English name from Devon,
associated with the accurse4d Normans!

Bob


"Roger Govier" wrote in message
...

Oops!!

Thank you Bob.
Because I had inserted a row to use the Subtotal function, I copied the
wrong range to my answer. You are quite right it should have been
the array formula
{=AVERAGE(IF($A$1:$A$1000="Wales",$B$1:$B$1000)) }

Lucky the English are wide awake!!!

Regards

Roger Govier


Bob Phillips wrote:

Be aware that this will probably give a wrong answer as it uses the

amount

in B2 when A1 equals Wales. For some reason, Roger shifted the second

range

down by a row.