Offset/Countif question
 

Good Afternoon,

I'm trying to get an Average for the Offset of a certain column. Column A
is labeled "Divisions" and cells A3 to A82 contain names. Column D is
labeled "Fav" and contains numeric percent values. I'm trying to only
Average the values in Columns D that have a value in Column A as "ous*".
I've provided an example (minus the other division name that do not include
ous) below and the current formula I'm using that returns the WRONG
percentage (comes up as 52 and should be 54).

I'd appreciate any advice or thoughts you have to reformulate my below
expression.

Thank you - Jenny B.


Column A. Column D.
Divisions - - - - Fav
Ous Fridley 88
Ous Edina 69
Ous Park 50
Ous Brown 32
Ous Grove 32
Ous West 27
Ous North 46
Ous South 65
Ous East 39
Ous Blvd 44
Ous Street 73

*** Current Formula
=IF(COUNTIF(A3:A82,"ous*"),AVERAGE(COUNTIF(A3:A82, "ous*"),OFFSET(A3:A82,0,3)))

Offset/Countif question
 

=SUMPRODUCT(--(LEFT(A3:A83,3)="Ous"),B3:B83)/SUMPRODUCT(--(LEFT(A3:A83,3)="O
us"))
Bob Umlas
Excel MVP

"Jenny B." wrote in message
...
Good Afternoon,

I'm trying to get an Average for the Offset of a certain column. Column A
is labeled "Divisions" and cells A3 to A82 contain names. Column D is
labeled "Fav" and contains numeric percent values. I'm trying to only
Average the values in Columns D that have a value in Column A as "ous*".
I've provided an example (minus the other division name that do not
include
ous) below and the current formula I'm using that returns the WRONG
percentage (comes up as 52 and should be 54).

I'd appreciate any advice or thoughts you have to reformulate my below
expression.

Thank you - Jenny B.


Column A. Column D.
Divisions - - - - Fav
Ous Fridley 88
Ous Edina 69
Ous Park 50
Ous Brown 32
Ous Grove 32
Ous West 27
Ous North 46
Ous South 65
Ous East 39
Ous Blvd 44
Ous Street 73

*** Current Formula

=IF(COUNTIF(A3:A82,"ous*"),AVERAGE(COUNTIF(A3:A82, "ous*"),OFFSET(A3:A82,0,3)
))

Offset/Countif question
 

Try this:

=SUMIF(A3:A82,"Ous*",D3:D82)/COUNTIF(A3:A82,"Ous*")

Or this array** formula:

=AVERAGE(IF(LEFT(A3:A82,3)="Ous",D3:D82))

** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Jenny B." wrote in message
...
Good Afternoon,

I'm trying to get an Average for the Offset of a certain column. Column A
is labeled "Divisions" and cells A3 to A82 contain names. Column D is
labeled "Fav" and contains numeric percent values. I'm trying to only
Average the values in Columns D that have a value in Column A as "ous*".
I've provided an example (minus the other division name that do not
include
ous) below and the current formula I'm using that returns the WRONG
percentage (comes up as 52 and should be 54).

I'd appreciate any advice or thoughts you have to reformulate my below
expression.

Thank you - Jenny B.


Column A. Column D.
Divisions - - - - Fav
Ous Fridley 88
Ous Edina 69
Ous Park 50
Ous Brown 32
Ous Grove 32
Ous West 27
Ous North 46
Ous South 65
Ous East 39
Ous Blvd 44
Ous Street 73

*** Current Formula
=IF(COUNTIF(A3:A82,"ous*"),AVERAGE(COUNTIF(A3:A82, "ous*"),OFFSET(A3:A82,0,3)))

Offset/Countif question
 

Thank you so very much. It is working flawlessly and is going to greatly
reduce our data calculating workload.

Take care and thank you as always for all of your help - Jenny B.

"T. Valko" wrote:

Try this:

=SUMIF(A3:A82,"Ous*",D3:D82)/COUNTIF(A3:A82,"Ous*")

Or this array** formula:

=AVERAGE(IF(LEFT(A3:A82,3)="Ous",D3:D82))

** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Jenny B." wrote in message
...
Good Afternoon,

I'm trying to get an Average for the Offset of a certain column. Column A
is labeled "Divisions" and cells A3 to A82 contain names. Column D is
labeled "Fav" and contains numeric percent values. I'm trying to only
Average the values in Columns D that have a value in Column A as "ous*".
I've provided an example (minus the other division name that do not
include
ous) below and the current formula I'm using that returns the WRONG
percentage (comes up as 52 and should be 54).

I'd appreciate any advice or thoughts you have to reformulate my below
expression.

Thank you - Jenny B.


Column A. Column D.
Divisions - - - - Fav
Ous Fridley 88
Ous Edina 69
Ous Park 50
Ous Brown 32
Ous Grove 32
Ous West 27
Ous North 46
Ous South 65
Ous East 39
Ous Blvd 44
Ous Street 73

*** Current Formula
=IF(COUNTIF(A3:A82,"ous*"),AVERAGE(COUNTIF(A3:A82, "ous*"),OFFSET(A3:A82,0,3)))

Offset/Countif question
 

You're welcome. Thanks for the feedback!

Biff

"Jenny B." wrote in message
...
Thank you so very much. It is working flawlessly and is going to greatly
reduce our data calculating workload.

Take care and thank you as always for all of your help - Jenny B.

"T. Valko" wrote:

Try this:

=SUMIF(A3:A82,"Ous*",D3:D82)/COUNTIF(A3:A82,"Ous*")

Or this array** formula:

=AVERAGE(IF(LEFT(A3:A82,3)="Ous",D3:D82))

** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Jenny B." wrote in message
...
Good Afternoon,

I'm trying to get an Average for the Offset of a certain column.
Column A
is labeled "Divisions" and cells A3 to A82 contain names. Column D is
labeled "Fav" and contains numeric percent values. I'm trying to only
Average the values in Columns D that have a value in Column A as
"ous*".
I've provided an example (minus the other division name that do not
include
ous) below and the current formula I'm using that returns the WRONG
percentage (comes up as 52 and should be 54).

I'd appreciate any advice or thoughts you have to reformulate my below
expression.

Thank you - Jenny B.


Column A. Column D.
Divisions - - - - Fav
Ous Fridley 88
Ous Edina 69
Ous Park 50
Ous Brown 32
Ous Grove 32
Ous West 27
Ous North 46
Ous South 65
Ous East 39
Ous Blvd 44
Ous Street 73

*** Current Formula
=IF(COUNTIF(A3:A82,"ous*"),AVERAGE(COUNTIF(A3:A82, "ous*"),OFFSET(A3:A82,0,3)))