+ Reply to Thread
Results 1 to 7 of 7

Inventory cost calculation

  1. #1
    Forum Contributor
    Join Date
    09-23-2020
    Location
    Turkey
    MS-Off Ver
    Office Pro Plus 2016 TR
    Posts
    140

    Inventory cost calculation

    Hello friends

    Can I ask your help about the attached table?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Inventory cost calculation

    Hello Tacir and Welcome to Excel Forum.
    This proposal employs a helper column (V) which may be moved and/or hidden for aesthetic purposes.
    Cells V5:V12 are populated using: =IF(S5=0,0,INDEX(F$4:F4,AGGREGATE(15,6,(ROW(F$4:F4)-ROW(F$3))/(H$4:H4>=-S5)/(B$4:B4=B5),1)))
    Cells N4:N12 are populated using: =IF($L4>=0,L4*F4,-SUMIFS(V$4:V4,B$4:B4,B4))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    09-23-2020
    Location
    Turkey
    MS-Off Ver
    Office Pro Plus 2016 TR
    Posts
    140

    Re: Inventory cost calculation

    JetecMc hello,

    There seems to be a problem with the calculations.Can you review again

    Thanks,

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Inventory cost calculation

    Try pasting the following into cell M4 and copying down: =IF($L4>=0,L4*F4,-INDEX(F$4:F$12,AGGREGATE(15,6,(ROW(F$4:F$12)-ROW(F$3))/(I$4:I$12>=SUMIFS(G$4:G4,B$4:B4,B4))/(B$4:B4=B4)/(I$4:I$12<>""),1))*G4)
    Also paste the following into cell N4 and copy down: =IF($L4>=0,L4*F4,SUM(M4*G4,SUMIFS(M$3:M3,G$3:G3,">0",B$3:B3,B4)))
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-23-2020
    Location
    Turkey
    MS-Off Ver
    Office Pro Plus 2016 TR
    Posts
    140

    Re: Inventory cost calculation

    Hello Jetemc

    I made a change in the document.I did not see any other problems in calculation
    Can you review again

    Regards,
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Inventory cost calculation

    It is good that you made the correction to the formula in column N and are getting the correct values. I am also glad to read that you do not see any other problems.
    Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  7. #7
    Forum Contributor
    Join Date
    09-23-2020
    Location
    Turkey
    MS-Off Ver
    Office Pro Plus 2016 TR
    Posts
    140

    Re: Inventory cost calculation

    Thanks, JeteMc


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 04-24-2015, 09:46 AM
  2. Inventory Valuation on Weighted Average Cost
    By pakhare.kiran in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-30-2013, 09:12 AM
  3. [SOLVED] Average Cost for Inventory
    By Ship4me1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 02:09 AM
  4. spreadsheet with product inventory cost and profit
    By hondaracerfl in forum Excel General
    Replies: 1
    Last Post: 01-13-2013, 12:11 PM
  5. Cost Driver calculation - Looking for formula/macro to calculate manpower cost alloca
    By Swastik Banerje in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-30-2009, 11:18 AM
  6. Calculate Cost Basis of Inventory
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-15-2008, 07:49 PM
  7. Factors governing Inventory Cost Calculation at dealership
    By Shankar Raman in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-04-2006, 05:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1