Daverage

[Guest]

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

 

[Bob Phillips]

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)

 

[Roger Govier]

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 Data>Filter>Autofilter and Select Wales for Column A and use the formula
=SUBTOTAL(1,B2:B1001)

Regards

Roger Govier

 

[Guest]

Why do you need the curley brackets?

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 Data>Filter>Autofilter and Select Wales for Column A and use the formula
=SUBTOTAL(1,B2:B1001)

Regards

Roger Govier

 

[Roger Govier]

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

 

[Bob Phillips]

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]

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]

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]

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]

Swanage ... without a morning's jog (chain ferry aside)!

Nobody washes up in Dorset, we're all here by choice (if not the choice of
us locals for many of the recent infiltrators).

And so where in our fair land are you from?

Bob