Adding & average calculating stock

[anu0512]

Hi, Q1- I want to add the 5-month stock & calculate the average stock. |I used consolidate feature in sheet 7 but "Item Description" & "Whse" field does not come in the field. Kindly tell me how to do that with consolidate feature & different function or which one will be easy. I gather all the data in 5-month inventory sheet. Q2- After calculating the Average stock unit of different item, I have another sheet were I have to put it average unit value, how I can use index match in that sheet.

 

[RasGhul]

You could use =SUMIFS to return values for each five month period and/or AVERAGEIFS, can you post some sample data?

 

[anu0512]

Please find the file link below. You check sheet "5 Months Inventory" it has all month data and I want to add or take an average of it.


https://ufile.io/a3btg

 

[RasGhul]

You could do your setup like the "5 Month Tab" in this copied version of your sheet, I've saved the file with a slightly different name to avoid going over your sheet.

https://docs.google.com/spreadsheets/d/1HHy0AmMSuAVvrjHxOVpGzrteSGR2QqO4pprDPNfzazI/edit?usp=sharing

 

[anu0512]

You could do your setup like the "5 Month Tab" in this copied version of your sheet, I've saved the file with a slightly different name to avoid going over your sheet. https://docs.google.com/spreadsheets/d/1HHy0AmMSuAVvrjHxOVpGzrteSGR2QqO4pprDPNfzazI/edit?usp=sharing

Thanks. Is this possible do the same thing from "5 Months Inventory" sheet ?

 

[RasGhul]

Hi Anu,

I made a new sheet as in your 5 month tab there is multiples of the same items so Index n match wont work properly there it should be a unique list unless you wanted to use dates gor items sold and then we can calculate your month values and averages with a different setup.

 

[anu0512]

Hi Anu,

I made a new sheet as in your 5 month tab there is multiples of the same items so Index n match wont work properly there it should be a unique list unless you wanted to use dates gor items sold and then we can calculate your month values and averages with a different setup.

Thanks.

Q1- Can you tell me like what this formula says means what you did ? Like have you done in any formula in monthly sheet too ?

Q2- From the next time I can do the same format ?

Q3- Now if I want to put this value in another sheet in the front of same item code what formula i can use or thus index match can use, in another sheet there is no repeatation of item code all the code are different.

 

[RasGhul]

Q1- Can you tell me like what this formula says means what you did ? Like have you done in any formula in monthly sheet too ?

When you make a new inventory month tab e.g insert a new row before the average column copy the following index & match formula in and change in the formula to your new tab name 30-06-18

=IFNA(INDEX('30-6-18'!$D$2:$D$607,MATCH('5 Month'!$A2,'30-6-18'!$A$2:$A$607,0)),"")
Q1- Can you tell me like what this formula says means what you did ? Like have you done in any formula in monthly sheet too ?

=IFNA - If the item is Not Available #N/A in 30-06-18 then leave a blank cell.

(INDEX('Lookup tab 30-6-18'!range $D$2:$D$607, and the get the quantity on hand value from column D

MATCH(from the 5 month tab find '5 Month'!"CE0003, lookup column A '30-6-18'!$A$2:$A$607,0)),"") this gives the row number of CE0003 to index

Quantity on hand in the 30-06-18 tab for CE0003 is 181. It doesn't matter what row this item appears in each tab as index & match is designed to find it and return the correct value.


Q3- Now if I want to put this value in another sheet in the front of same item code what formula i can use or thus index match can use, in another sheet there is no repetition of item code all the code are different.

What you need to make sure is that 1 of every item in your product list appears in the 5 month sheet so that index & match can work. No formulas are needed in your month sheets as they are you're lookup data only.

 

[anu0512]

Hi,

I have put the avg value in Sheet1 from the avg sheet.

https://ufile.io/bzlrj

 

[RasGhul]

In G2,

=IFNA(INDEX(Average!$D$2:$D$606,MATCH(Sheet1!A2,Average!$A$2:$A$606,0)),"")