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

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gene Haines" 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.

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)

"Gene Haines" wrote in message
...

Max Wrote:
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gene Haines" 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

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
B1:D1
Labels in B2:D2 : 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 B1:D1 : 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 B2:D2, 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gene Haines" wrote:
.. 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" 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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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




--
Gene Haines

Bob: I am new to posting so I should have made myself a little more clear. For each week of the year I have 3 column headings,156 columns 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 the columns only those that have the Inventory heading. I used your setup and it worked for which I thank you very much. I was not all that familiar with arrays and how to execute them.

Regards

Gene

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)

"Gene Haines" wrote in message
...

Bob Phillips Wrote:
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" 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




--
Gene Haines-
Bob: I am new to posting so I should have made myself a little more
clear. For each week of the year I have 3 column headings,156 columns
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 the
columns only those that have the Inventory heading. I used your setup
and it worked for which I thank you very much. I was not all that
familiar with arrays and how to execute them.

Regards

Gene




--
Gene Haines

Gene, hope that you will reply further to us. I've posted my take on your
situation in the other branch.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
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)