Offset/Countif question

[Jenny B.]

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)))

 

[Bob Umlas]

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

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)
))

 

[T. Valko]

Try this:

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

Or this array** formula:

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

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

Biff

 

[Jenny B.]

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]

You're welcome. Thanks for the feedback!

Biff

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.