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

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

Hi
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.

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.

Cheers!

2. ## 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.

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)

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

#### 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