Adding & average calculating stock

anu0512

New Member
Joined
Feb 19, 2019
Messages
6
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
You could use =SUMIFS to return values for each five month period and/or AVERAGEIFS, can you post some sample data?
 

anu0512

New Member
Joined
Feb 19, 2019
Messages
6

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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

New Member
Joined
Feb 19, 2019
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Jul 15, 2016
Messages
609
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.
 
Last edited:

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
In G2,

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

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,939
Members
409,848
Latest member
Blomsten
Top