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.
Last edited by Par; 10-12-2014 at 06:56 PM.
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)
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By exlecel123 in forum Excel Programming / VBA / Macros
Last Post: 10-26-2013, 06:12 AM
By Len12 in forum Excel Formulas & Functions
Last Post: 08-19-2013, 11:28 PM
By check00 in forum Excel Formulas & Functions
Last Post: 06-29-2012, 08:41 AM
By dwhite30518 in forum Excel Programming / VBA / Macros
Last Post: 05-14-2012, 10:43 AM
By cmagalha in forum Excel Programming / VBA / Macros
Last Post: 01-15-2009, 06:09 AM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1