# Adding & average calculating stock

#### anu0512

##### New Member
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.

### 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
You could use =SUMIFS to return values for each five month period and/or AVERAGEIFS, can you post some sample data?

#### anu0512

##### New Member
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

##### Well-known Member
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

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
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
In G2,

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

Replies
3
Views
56
Replies
12
Views
209
Replies
1
Views
47
Replies
5
Views
568
Replies
4
Views
41