 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2006Averaging Columns based on a Text String
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 -- 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>)  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > > Hello: I am attempting to average columns that have headings of [quoted text clipped - 7 lines] > Thank you > Gene Haines Max Wrote: > Maybe you could paste a sample of your set-up in plain text in reply > here to [quoted text clipped - 19 lines] > 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. SignatureGene Haines How do you know what is first week as against second etc.? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Max Wrote: > > Maybe you could paste a sample of your set-up in plain text in reply [quoted text clipped - 33 lines] > my inventory turns. Hope this clarifies what I am attemping to do. > Thanks for your response. 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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > .. Max: Here you go. > Item Inventory Sept Sales Sept Purchases Inventory Oct [quoted text clipped - 7 lines] > my inventory turns. Hope this clarifies what I am attemping to do. > Thanks for your response. > > Hello: I am attempting to average columns that have headings of > > Inventory. My spreadsheet consists of the following column headings: [quoted text clipped - 7 lines] > > 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. SignatureMax 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. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Hello: I am attempting to average columns that have headings of > Inventory. My spreadsheet consists of the following column headings: [quoted text clipped - 7 lines] > > Gene Haines Bob Phillips Wrote: > Shouldn't you use > [quoted text clipped - 16 lines] > > 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 -- Gene Haines Gene, Does that mean that you are sorted, or do you still need assistance? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Bob Phillips Wrote: > > Shouldn't you use [quoted text clipped - 36 lines] > > Gene Gene, hope that you will reply further to us. I've posted my take on your situation in the other branch. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Gene, > > Does that mean that you are sorted, or do you still need assistance?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.