Tough Inventory Weighted Average Price Problem, Please Help!

hi there,

interesting problem - not due to the nature of the problem itself, but due to the use of Excel Smart Table; turns everything on its head.

there were two problems that you were seeking assistance with - On Hand Quantity and Average Costing; i was able to solve only the OHQ piece...

there are two ways of going about it, both will substantially simplify your existing formula in the Purchases column on Master Inventory tab. however, first you must work on baselining your template across all the "month" sheets - some have the Arrived? column, some dont, some have Unit Cost before MOH, some have just Unit Cost. you have to ensure that every single one of those "month" sheet has all the columns that you need for calculations on the Master Inventory sheet and that the names of those columns match exactly.

now, there are two ways of solving the OQH problem (cell J2 of Master Inventory sheet):

Please Login or Register  to view this content.

or, create a Named Range called "tabs" (w/o quotes) and put the following array in the Refers To field, and then save the record.

Please Login or Register  to view this content.

then, use the following formula:

Please Login or Register  to view this content.

the second option is more robust since it will not be dependent on Date Format set on your computer.

i hope someone is able to assist you with the Average Costing situation.

UPDATE:

here is a helper-column-assisted solution for the Average Costing situation.

in every "month" table, create a new column called UCFP (you can call it whatever you want, but just keep the name consistent). the formula in this column would be:

Please Login or Register  to view this content.

and, this formula would have to be repeated for every row of every "month" sheet.

now, on the Master Inventory sheet, put the following formula in the Unit Cost column:

Please Login or Register  to view this content.

if you want to hide errors, you can always use IFERROR wrap-around.