Averaging Columns based on a Text String

[Gene Haines]

Hello: I am attempting to average columns that have headings o
Inventory. My spreadsheet consists of the following column headings
Inventory, Sales, Purchases. Each week I run a query that post
Inventory, Sales and Purchases for 450 Inventory items.How can
average the column heading of Inventory for the 52 weeks across thes
column headings. I tried using average(if(a1:a52 ="Inventory", a2:a52
but it doesn't work. Any help would be appreciated.

Thank you

Gene Haine

 

[Max]

Maybe you could paste a sample of your set-up in plain text in reply here to
clarify what you really have over there.

Your attempted formula:

average(if(a1:a52 ="Inventory", a2:a52) doesn't quite gell with:
... the following column headings:
Inventory, Sales, Purchases

(haven't worked in your 450 items and 52 weeks yet <g>)

 

[Bob Phillips]

Shouldn't you use

=AVERAGE(if(a1:a52 ="Inventory", B2:B52))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gene Haines]

Maybe you could paste a sample of your set-up in plain text in reply
here to
clarify what you really have over there.

Your attempted formula:-
average(if(a1:a52 ="Inventory", a2:a52)-
doesn't quite gell with:-
... the following column headings:
Inventory, Sales, Purchases-

(haven't worked in your 450 items and 52 weeks yet g)

"Gene Haines" (e-mail address removed) wrote in message


Hello: I am attempting to average columns that have headings of
Inventory. My spreadsheet consists of the following column headings:
Inventory, Sales, Purchases. Each week I run a query that posts
Inventory, Sales and Purchases for 450 Inventory items.How can I
average the column heading of Inventory for the 52 weeks across these
column headings. I tried using average(if(a1:a52 ="Inventory",
a2:a52)
but it doesn't work. Any help would be appreciated.

Thank you
Gene Haines -

Max: Here you go.


Item Inventory Sept Sales Sept Purchases Inventory Oct
Sales Oct Purchases
ABC 390 171 565 785 272
295

As an example: I am trying to average across these columns, the
inventory for the last week in Sept and the first week in October. I
will use the average at the end of 52 weeks for each item to determine
my inventory turns. Hope this clarifies what I am attemping to do.
Thanks for your response.

 

[Gene Haines]

Shouldn't you use

=AVERAGE(if(a1:a52 ="Inventory", B2:B52))

which is an array formula, it should be committed wit
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gene Haines" (e-mail address removed) wrote in message

Hello: I am attempting to average columns that have headings of
Inventory. My spreadsheet consists of the following column headings:
Inventory, Sales, Purchases. Each week I run a query that posts
Inventory, Sales and Purchases for 450 Inventory items.How can I
average the column heading of Inventory for the 52 weeks across these
column headings. I tried using average(if(a1:a52 ="Inventory"
a2:a52)
but it doesn't work. Any help would be appreciated.

Thank you

Gene Haines

Bob: I am new to posting so I should have made myself a little mor
clear. For each week of the year I have 3 column headings,156 column
in total. As an example.
Item Inventory, 1st Week Jan Sales, 1st week Jan Purchase
Inventory, etc, etc. 52wkavg
ABC 365 456 192
356 432 176
DEF 213 125 234
256 139 142

It is in the 52wkavg column that I am trying to average across th
columns only those that have the Inventory heading. I used your setu
and it worked for which I thank you very much. I was not all tha
familiar with arrays and how to execute them.

Regards

Gen

 

[Bob Phillips]

How do you know what is first week as against second etc.?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Gene,

Does that mean that you are sorted, or do you still need assistance?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Max]

Gene,

Thanks for response. I see that you've got some responses from Bob. Here's
my take on what you have over there, and what your intent probably is ..

A sample construct is available at:
http://www.savefile.com/files/87109
YTD averaging across repeated cols.xls

Source data is assumed in a sheet named: X,
Label in B1: Oct 2006 (text), with B1's label centred across selection in
B11
Labels in B22 : Inv, Sale, Pur
Structure above is repeated (3 cols at a go) across for the full year (12
months) till col AN. The 450 items are listed in A3 down, eg: ABC, Item2,
Item3, etc.

In a new sheet Y,
Labels in B11 : Inv, Sale, Pur
450 items listed in A2 down: ABC, Item2, Item3, etc
(presumed to be in the same order as in X)

Inventory:
Array-entered (press CTRL+SHIFT+ENTER) in B2:
=AVERAGE(IF((MOD(COLUMN(X!B3:AN3),3)=2)*(X!B3:AN3<>""),X!B3:AN3))
will return the average of all the 12 monthly inventory cols in X (between
cols B to AN) for ABC (The average will be the "YTD" fig, assuming source
data is filled in from left-to-right in X.)

Sales:
Array-entered (press CTRL+SHIFT+ENTER) in C2:
=AVERAGE(IF((MOD(COLUMN(X!B3:AN3),3)=0)*(X!B3:AN3<>""),X!B3:AN3))
will return the average of all the 12 monthly sales cols in X (between cols
B to AN) for ABC (same formula as for inventory, except with the MOD result
=0 instead)

Purchases:
Array-entered (press CTRL+SHIFT+ENTER) in D2:
=AVERAGE(IF((MOD(COLUMN(X!B3:AN3),3)=1)*(X!B3:AN3<>""),X!B3:AN3))
will return the average of all the 12 monthly purchase cols in X (between
cols B to AN) for ABC (same formula as for inventory, except with the MOD
result =1 instead)

Then just select B22, copy down to return correspondingly for all the
other 450 items. Adapt to suit ..

Note: Visually check that formula is correctly array-entered. Look in the
formula bar, you should see curly braces { } wrapped around the formula.
These are auto-inserted by Excel. If you don't see these braces, you haven't
array-entered correctly. Wrong results will be returned if the formulas are
not array-entered.

 

[Max]

Gene, hope that you will reply further to us. I've posted my take on your
situation in the other branch.

 

[Max]

Gene, noted you post from Excelbanter. From my past observations, Excelbanter
inevitably removes all operators/symbols for "more than", "less than" or "not
equal to". So any formulas posted which have these operators within will
definitely not appear right in Excelbanter (like the ones I posted). Please
d/l & see the working sample file posted for the correct formulas implemented.