Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Daverage
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 |
#2
|
|||
|
|||
Daverage
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 |
#3
|
|||
|
|||
Daverage
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 |
#4
|
|||
|
|||
Daverage
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 |
#5
|
|||
|
|||
Daverage
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 |
#6
|
|||
|
|||
Daverage
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 |
#7
|
|||
|
|||
Daverage
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. |
#8
|
|||
|
|||
Daverage
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. |
#9
|
|||
|
|||
Daverage
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. |
#10
|
|||
|
|||
Daverage
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 "Roger Govier" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DAverage Function help needed | Excel Worksheet Functions | |||
How do I use DAverage in Excel? | Excel Worksheet Functions | |||
How do I use DAverage in Excel? | Excel Worksheet Functions | |||
daverage problems | Excel Worksheet Functions |