• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find average on a row spanning many columns?

S

smc001

Member
I have large spreadsheet with many rows and columns... Each row is a product. the columns adjacent list., column 1:location, column2:# products in stock . column3:cost of product... Im trying to find the average product cost which is gathered from all the locations total spanning all locations.


Im running into an issue that some times there may be a product not in stock so it's zero, yet there's a price associated...What formula can I use to pull data from the cells of that row that have the number of products in stock with a dollar value associated? or can I just elongate if product in inventory is zero ignore product price in column 3....


Any and all in for is Greatly appreciated... God Bless
 
I'm not sure that's a question we can answer. It will depend on what your metric needs to be. If you want the average cost of a particular product for all locations, then individual quantities shouldn't matter (average=SumPrices/TotalStores). If you really need average price for all parts, then qty does matter. But, if qty matters, then a inventory of 0 truly does generate $0, so your current setup is correct (average=Sum(Prices*Qty)/TotalQty.)
 
Row1-row500: Product Names,


Store A - Column2: # of products in inventory... sometimes zero

Store A - Column3: Cost of Products...sometimes zero (if price has not been key'd)


Store B - Column4: # of products in inventory... sometimes zero

Store B - Column5: Cost of Products...sometimes zero (if price has not been key'd)


Store C - Column6: # of products in inventory... sometimes zero

Store C - Column7: Cost of Products...sometimes zero (if price has not been key'd)


Store D - Column8: # of products in inventory... sometimes zero

Store D - Column9: Cost of Products...sometimes zero (if price has not been key'd)


Store e - Column10: # of products in inventory... sometimes zero

Store e - Column11: Cost of Products...sometimes zero (if price has not been key'd)


etc... for each store.


Column 100: I want to average all costs of products in columns 3,5,7,9,11.


(I only want to average those columns if columns 2,4,6,8,10 has an inventory value of 1.0 or greater, if 0 zero value I don't want to add to the average calculation. I feel like I should be using a helper column???


Yes I need an average price for all parts for all locations, except location with zero in inventory must be excluded.


Is this a better picture? Im only trying to use a formulas and not an array.


Thanks again for your feed back and additional ideas would be greatly appreciated.
 
Smc001


Can you post a sample work book/worksheet so we can clearly understand the layout and requirement

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


I'm going to suggest that it can be done using the Sumproduct() function and a technique similar to that described here: http://chandoo.org/wp/2011/11/30/formula-forensics-no-004/
 
Hi ,


Can you try this , entered as an array formula , using CTRL SHIFT ENTER :


=SUM(IF(N(OFFSET($B2,0,(COLUMN($B2:INDEX(2:2,COLUMNS($B2:$Q2)/2+1))-2)*2,1,COLUMNS($B2:$Q2)/2))>0,N(OFFSET($B2,0,1+(COLUMN($B2:INDEX(2:2,COLUMNS($B2:$Q2)/2+1))-2)*2,1,COLUMNS($B2:$Q2)/2))))/SUM(IF(N(OFFSET($B2,0,(COLUMN($B2:INDEX(2:2,COLUMNS($B2:$Q2)/2+1))-2)*2,1,COLUMNS($B2:$Q2)/2))>0,1,0))


I have assumed the following :


Data is in the range B through Q ; if your data extends beyond this range , modify the references to $B2 and $B2:$Q2 accordingly.


This makes use of a technique given by Sajan at this link :


http://chandoo.org/forums/topic/dynamic-charts-with-non-contiguous-data


Narayan
 
I tried the formula and have DIV# errors... I have uploaded the sample doc as per requested HUI...Heres the link to my sample in drop box....


http://dl.dropbox.com/u/15655422/Sample.xlsx


Thank you!!!!! (any other suggestions are eagerly welcomed!!!
 
Ate you wanting me to test accessibility? It should open for you...Let me know if not I can send via email... ;)
 
You must log in or register to reply here.
Back
Top