+ Reply to Thread
Results 1 to 2 of 2

FIFO-LIFO UDF function not working if DataRange are apart from each other

  1. #1
    Registered User
    Join Date
    California, USA
    MS-Off Ver
    Excel 2010

    FIFO-LIFO UDF function not working if DataRange are apart from each other

    I found the FIFO-LIFO UDF Function code incorporated into the attached workbook on this forum here: http://www.mrexcel.com/forum/excel-q...tion-help.html . It was written by someone referred to as “al_b_cnu”. I must say it is one of the best FIFO-LIFO function I’ve evaluated and can adapt for my use since you can use it in a Trading Journal for buying/selling Stocks and Mutual Funds alongside (see Column H on Sheet2) each sale transaction.

    I’ve shown the original structure and use of the FIFO-LIFO function in Sheet1 of the attached Workbook. On Sheet2, I’ve shown my adaptation in a slightly different structure; but limited to how the function works now. The function works as follows:

    =FIFO("A",100,B2:C10) produces, FIFO(A,100,B2:C10); 10 @ $10; 40 @ $11; 50 @ $12; Total=$1140 as the result for FIFO
    Where “A” is the Product, 100 is the quantity sold, and B2:C10 is the range in which the Product and Cost are listed.

    =LIFO("A",100,B2:C10) produces, LIFO(A,100,B2:C10); 80 @ $12; 20 @ $11; Total=$1180 as the result for LIFO

    NOTE: The parameters for both the FIFO and LIFO function can be cell referenced. I’ve shown cell references in Sheet2.

    My ask is as follow in order of priority:

    1. Firstly and ideally, I’d like to use the structure shown in Sheet3 (..this structure represents a typical journal/log of trading transactions) where I can use the FIFO and LIFO functions in Column N under the heading, “Cost of Shares Sold (FIFO/LIFO)”. Note: the original structure (Sheet1) requires that “Product” and “Unit Bought” are two Columns next to each other. In Sheet3, I’m showing “Product” or “Ticker Symbol” in Column E and “Unit Bought” or “No. Of Shares Purchased” in Column K. These two columns can be variable, meaning, there can be any two columns; but if not possible, Column E and Colum K are OK with me.
    2. Secondly, as an option in the call to the function, I’d like the “Total” cost of shares to be listed as “FIFO Total = $1140” as an example; or “LIFO Total = $1180” as examples. Both without the quotes. So, in the case of the original call to the function, we could have =FIFO("A",100,B2:C10,Spellout”) to produce, FIFO(A,100,B2:C10); 10 @ $10; 40 @ $11; 50 @ $12; Total=$1140 and, =FIFO("A",100,B2:C10,”Total Only”) to produce, FIFO Total = $1140. The same for the LIFO function. AND, =LIFO("A",100,B2:C10, Spellout) to produce, LIFO(A,100,B2:C10); 80 @ $12; 20 @ $11; Total=$1180 and, =LIFO("A",100,B2:C10, Total Only) to produce LIFO Total = 1180

    Can someone help me with request 1 and 2 above? I would really appreciate it and I think a “thousands & more” people will as well since it will be an excellent/useful UDF function presented by this forum.

    Please make changes to the workbook UDF code as needed and re-post so I can download it to obtain the code for testing and use. Any questions, please let me know.

    Many thanks.

    Attached Files Attached Files
    Last edited by Par; 10-12-2014 at 06:56 PM.

  2. #2
    Forum Guru
    Join Date
    Los Angeles, Ca
    MS-Off Ver

    Re: VBA code modifications Needed to FIFO-LIFO UDF function in attached workbook

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Ben Van Johnson

+ 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. Modifications to code
    By exlecel123 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-26-2013, 06:12 AM
  2. Trying to compute lifo and fifo and average cost on investments
    By Len12 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-19-2013, 11:28 PM
  3. [SOLVED] Please help with function extracting specific data -attached workbook
    By check00 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-29-2012, 08:41 AM
  4. [SOLVED] Pivot Table Code Modifications
    By dwhite30518 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-14-2012, 10:43 AM
  5. Closing a Workbook, discard modifications
    By cmagalha in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-15-2009, 06:09 AM


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