+ Reply to Thread
Results 1 to 10 of 10

FIFO Excel Formula help

  1. #1
    Registered User
    Join Date
    04-21-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    7

    FIFO Excel Formula help

    Hi All, I've tried searching the forum for some help on creating this spreadsheet but to limited avail.

    fifoattempt_updated.xlsx

    Basically, what I am trying to achieve using this workbook is that, every time we sell apples (column D) we are depleting the apples that were purchased first (column C). This way, column E tells us how many apples are remaining for each of the apple purchases (ie. how many remaining of the original 300 then the additional 200).

    The main stipulation that makes this a little bit difficult is that, each 'purchase' only lasts for 30 days.
    • I have column B showing each purchases' expiration date.
    • in my attached example, on 3/10/2014 (cell J1), we would expect for 50 remaining (and now expired) from the original purchase because all "sold apples" that occurred between 1/1 and the expiration date of 1/31 were only 250 apples.
    • Then the second purchase has a remaining 100 apples due to apples sold on 2/3.
    • lastly, the final purchase on 2/3 still has all of their apples since the second purchase has not been depleted or expired.

    Sorry if my explanation is a little fuzzy, please see the attached workbook for what I'm trying to get at. Let me know if you have any additional questions. I prefer not to use VBA for this and make it completely formula driven; if you think this might be too difficult, let me know.

    I tried to input a formula into column E but it wasn't working out too well, haha.

    Thank you in advance!
    Last edited by frigate; 10-23-2014 at 09:27 AM.

  2. #2
    Registered User
    Join Date
    10-23-2014
    Location
    nakuru, kenya
    MS-Off Ver
    7
    Posts
    1

    Re: FIFO Excel Formula help

    I would love to know this one

  3. #3
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: FIFO Excel Formula help

    I don't understand your example, as it seems that the second purchase of 150 has been counted twice. It formed part of the 250 that were sold from the first purchase of 300, but then you've also deducted it from the purchase of 200. What have I missed?

  4. #4
    Registered User
    Join Date
    04-21-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: FIFO Excel Formula help

    Larena,

    apologies; you're right. I have attached an updated file with the correct expected results. Thanks for pointing that out. (Link changed in topic)

  5. #5
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: FIFO Excel Formula help

    Hmmm.. I think I've cracked the formula, but I don't get the same answers as you. When I work it out logically, or with the formula I get the answers 50,250,200

    =IF(C2>0,SUMIFS(C:C,A:A,">="&(A2-30),A:A,"<="&A2)-SUMIFS(D:D,A:A,">="&A2,A:A,"<="&A2+30),"")

    The logic is:
    • If there's a number in the Purchased column
    • then work out the total number of apples purchased within the last 30 days
    • and deduct the sales for the 30 day range from that


    Spreadsheet attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: FIFO Excel Formula help

    Whoops - I see where I went wrong. Redoing it & will upload another version shortly

  7. #7
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: FIFO Excel Formula help

    Sorry - I think I'm going to have to concede defeat on this one! It feels like there should be something obvious but I just can't work out how to track which sales relate to which delivery.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: FIFO Excel Formula help

    see the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: FIFO Excel Formula help

    Well done nflsales!

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: FIFO Excel Formula help

    Thank you Larena for your feedback and reputation

+ 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. FIFO COGS formula
    By seolann in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2013, 06:41 PM
  2. Fifo formula in microsoft excel spreadsheet
    By xze82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-28-2013, 10:01 PM
  3. FIFO Formula
    By kishanghataliya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2013, 09:49 PM
  4. Using FIFO Formula on a Worksheet
    By hdanger10 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2012, 02:39 PM
  5. Query on Excel Formula -FIFO Accounting
    By sabya in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2011, 10:50 AM

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