Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi, I want to make an Excek-system in a certain format. However, I have some problems making it. I hope the experts on this site can help me solve the problem. (possibly using VBA). The right material has to be found automatically and the right price must be calculated according the use of the materials. I have posted an attachment with description at

Who can help me?

Regards, Exelhenk

View Answers     

Similar Excel Tutorials

Quickly Enter the System Date in Excel - Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Excel Formatting for Zip Codes and Phone Numbers
Display numbers in Excel as zip/postal codes and phone numbers in order to make them easier to read and retain any ...

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Output the File Path to and Name of a Workbook in Excel - UDF
- Free Excel UDF (user defined function) that displays the full file path and name of an Excel workbook. This function is
Format Cells as a Fraction in Excel Number Formatting
- This free Excel macro will automatically format a selected cell or many selected cells in the Fraction number format in
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics

I am trying to interpolate between data. Imagine the following situation:

Stock trade: Trade Price: Consideration: FIFO Cash:
+100,000 2.50 250,000 0
+50,000 2.75 137,500 0
+225,000 3.00 675,000 0

Then I sell: -184,250 @ 3.40
The resultant cash that is due from these trades is calculated on a FIFO
basis (First In First Out). So the calculation would be (100,000
*(3.40-2.50) + 50,000 *(3.40-2.75) + 34,350 * (3.40-3.00)) = 136,240. But I
want to add a column that does this automatically. I.e.:
Stock trade: Trade Price: Consideration: FIFO Cash:
+100,000 2.50 250,000 0
+50,000 2.75 137,500 0
+225,000 3.00 675,000 0
-184,250 3.40 626,450 +136,240
The cash line would also need to be able to go the other way... i.e. if had
sold first and then hit a buy, what is the cash flow on a FIFO basis.

Any ideas how I can do this??


As enclosed in attachment, I want the same result but more improvised ways, as it is too complicated to understand & use.Since this is the sample with less data which I found in website( ")
.As I have a large data, if this is done than large data case too might also get solved.For anyone to understand the case is as follows :

"Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices. On another sheet, the closing stock of each Raw material type is entered. In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued is from the oldest stock lying in inventory i.e. the inventory left over will be from the recent stock purchased.

The objective is to determine the value of the closing stock of Raw material on FIFO basis.

Anyone who is in accounts field can understand easily without explaining all above.

Hope now it has been understood from above explanation and this might can solve this problem.

With Best Rgds,

Dear all,

It gives immense pleasure to stay as member of this forum as it helps to solve all my difficulties in excel in a matter of one day.

Thank you all .

I still pose one the problem of FIFO consumption valuation.In the enclosed file purchases are recorded in "input-Purchase" sheet with their landed costs.

The material is consumed on day to day basis and i want to calculate the consumption value based on FIFO basis at column "D2" onwards on sheet "Output-FIFO consumption".

I understand the difficulty faced my me to arrive at consumption value for each day.

Thank you all in adavnce


Hello Everybody,

I found this UDF called as FIFO on internet . I tried to use it. This UDF calculates inventory value on FIFO basis. It worked perfectly excet in one instance( marked in yellow colour in attached file.) can anybody help me what is wrong. I understand VB very little. Can record macros but no difficult programming. This UDF will help me a lot if this problem is solved.

Can anybody help me?


I am hoping this is an esay thing to fix but I have found a FIFO function to use in Excel and it doesnt seem to work. My VBA is basic and I cant step through the code as it is a function. All I basically want to calculate is the FIFO value of the sale quantity. As you will see in the attached its calculating some weird values.

For the Sale on the 1st Nov I would expect the following:

323 x 640 = 206,720

For the Sale on the 2nd which goes across two inventory values I would expect the following:

3,409 x 640 = 2,181,760
392 x 668 = 261,856
= 2,443,616 as sale USD value FIFO and then this would come off the closing value.

Thanks for your help.


I found this UDF VBA code to calculate FIFO on this Site: and I need to know how ti install it for use on a new Excel Workbook.

I've been able to use and play around with it within the .xlt workbook provided; but now, I'd like to transport it to my own workbook and it it there.

Can someone help me with this. I'd appreciate i very much.



I am trying to calculate a current position and a Weighted Average Price using teh FIFO methodology.

the attachment shows the data has a long (or positive) position of 56 if you total hte buys and sells.

Could you help me do this calculation using excel functions, ideally without excel VB but if that is the only soloution then great.

Thanks Craig

I'm trying to value inventory using FIFO method. I have multiply "layers" of values. Example is I have an inventory of 1,000 lbs. to value. Below are the last three transactions with prices paid.

Dec 15 - 200 lbs @ $1.25
Dec 10 - 500 lbs @ $2.00
Dec 8 - 500 lbs @ $1.50

Doing the math using FIFO method the 1,000 lbs should be valued at $1.70/lb or $1,700 total (200x1.25 + 500x2.00 + 300x$1.50).

I'm looking to create a formula that can be used every month that will calculate from a list similar to above where column A will be the date, column B the lbs, and column C the price/lb. I could add another column (D) that would be total price if that helps. I would like to just be able to enter the lbs of inventory I'm needing to value and have a fomula calculate the FIFO value based of the data above.

Hope this makes sense.

Hi all. First of all, I would like to mention that I've only worked with excel for a couple of days, and I need some advice.

My problem: there is a building with 1 elevator, 11 floors and 140 passengers of which we know their arrival time, their destination and their arrival floor. I now have to calculate the sum of the service times (= waiting time + traveltime) of all passengers for 2 heuristics: FIFO and circular.

FIFO(first in first out): the first passenger will be serviced first, the second second, etc... So,we deal with every passenger individually, so at no time, there is more than one passenger in the elevator.

Circular: the elevator goes up to the top floor and picks up everybody going up on his way. When the elevator reaches the top, he goes back down and now picks up everybody going down. In this case, MORE than 1 passenger can be in the elevator at the same time

For the FIFO case, I already managed to calculate the total service time. But for the circular heuristic, I'm really stuck..

Does anyone have suggestions?

I uploaded the data, and my solution for FIFO as an example. The original data is in red. The black data is my solution for the fifo problem.

Thx in advance!


I have a spreadsheet that has historical buy and sell transactions of securities ('Trade Blotter' tab) and this information is summarised in the 'Filter' tab by security (that is, if there were 3 transactions for CBB historically, cell B3 in 'Filter' shows how many shares of CBB are currently owned and what the total cost has been on an aggregate level.) The final piece of information I'm looking for on an aggregate level is the cost per unit (which is not simply cost divided by shares, but is using the FIFO method). I have calculated it using the FIFO method on the 'FIFO' tab, cell C8. FIFO tab shows the historical transactions of CBB -in this example, it is assumed that the 400 shares sold are the first 400 shares purchased (thus 221 from the first transaction and 189 from the 2nd transaction) at those prices. Thus, in this case, I have 262 shares remaining, all from the 2nd transaction, and they were all purchased at $113.28.

What I am looking to do is to put the total cost value from cell D8 of the 'FIFO' tab in cell F3 on the 'Filter' tab. Thus, I'm looking to consolidate the calculations in the FIFO tab. Would anyone be able to help with this - I believe it would be through the use of multiple arrays in one cell as I've done in column D of the 'FIFO' tab.

Thanks in advance,


Hello, I hope someone can help me with this, I have a inventory management worksheet for several products and I want to use the First In, First Out system for this.

This is the layout for the Initial Inventory:

Code / Quantity / Unitary Cost
00-001 / 200 / $80

This is the layout for inventory In:

Code / Quantity / Unitary Cost
00-001 / 400 / $100
00-001 / 600 / $150

This is the layout for inventory Out:

Code / Quantity / Formulated Unitary Cost
00-001 / 400 / Formula Result = $90
00-001 / 400 / Formula Result = $125

Is there a way using either excel formulas or VBA code to calculate the FIFO system?
I appreciate any help you could offer, thanks!

how to write a formulas to calculate FIFO cost in columm G when stock is out base on the attached, please help me!


* A B C D E F G H 2 prodcode date in out price Bal_Q cost$ * 3 A 30-Oct-10 9 * * * * * * * 9.00 * * * * * * * * * 9 * * 4 A 30-Oct-09 300 * * * * * *10.00 * * * * * * *309 * * 5 A 15-Jan-10 * * * * * * * *200 * * * * *11.00 * * * * * * *109 * *1,991.00 * 6 A 19-Jan-10 * * * * * * *400 * * * * * *10.00 * * * * * * *509 * * 7 A 19-Jan-10 * 150 * * * * *12.00 * * * * * * *359 * *1,500.00 * 8 A 27-Jan-10 * * * * * * * *100 * * * * *10.50 * * * * * * *259 * *1,000.00 * 9 B 27-Jan-10 * * * * * * *100 * * * * * *10.50 * * * * * * *100 * * 10 B 30-Mar-10 * * * * * * * *100 * * * * *11.00 * * * * * * * * - * * *1,050.00 * 11 * * * * * * * * 12 * * * * * * * *

Excel tables to the web >> Excel Jeanie HTML 4

Thanks in advance!


Good day to you all.

Please help to to solve this perinial problem of FIFO valution.
I have a sheet named "Inputdata-Purchse" where the landed cost are recorded for all products for entire year.
I have to calculate the FIFO value for the closing stock as on particular date or on the last date of month.
Sample for the month of april and may is shown as output in their respective sheet by doing it manually which took quite a lot of time and with 100% scope of error.

Somebody help me to get the things easy way to do it.

Thanks in advance.



I've been trying to come up with either a formula or a macro (I'm a novice in VBA so it would probably take me forever to figure it out) to track the FIFO balance (First In-First Out) of items at any given point in time. I have attached a sample spreadsheet with the example and all the notes that might help figuring this out.
I want to stress that I'm not trying to calculate any cumulative balance or any FIFO pricing whatsoever; just separate tracking of the purchase balances at any point in time after varous sales using the FIFO balance.
Any help is greatly appreciated.


Hi. This is another FIFO question.

I would like to get a FIFO cost of my stock portfolio which can have both positive and negative inventory (I mean Long position and short position).

Date Item Buy/Sell Price Cost 1-Jan Apple 200 80 80 1-Jan Banana 1000 25 25 15-Jan Apple 400 100 93.33 1-Feb Apple -400 90 100 15-Feb Apple 200 90 95 1-Mar Apple -600 100 100

Date is a date on which I bought or sold.
Item is an item. Company
Buy/Sell positive number is purchase and negative number is a sales.
Price is a price at which I bought or sold the share.
Cost is the number I would like to get using UDF.

Functions should look like this =FIFO(items,buy/sell,price)
Table should be sorted in chronological order beforehand.

Any help appreciated. Thank you very much.

Hello, I have developed a FIFO stock profit sheet for both longs and shorts. The Long transactions seem to be working fine but the short transactions not so much.
I am attaching the worksheet. If you play around with the quantities bought and sold you can see the short FIFO is wrong. Any ideas?

Hi All,
I am trying to value inventory on FIFO basis (atleast to nearest possible accurate level). I have exported & doctored few reports from Quick Books (accounting s/w). On attached sheet on FIFO details tab I caputred on hand inventory, received between 7-1-09 to 8-18-09 PO by PO details on PO cost tab with other cost overhead items broken down. what i would like to do.. on FIFO details tab- for on hand inventory- find rate or average rate starting from the last received PO. see example shown on line 11 on FIFO Details sheet. can any one provide any code or formula to achieve this? # PO received column does not have much significance, since i was trying to check how many po's are making the total received qty's. Please help


Good day to you all.

I have a problem of FIFO calculation basedon the "Purchase book Master" and "closing Balance FIFO".

Ineed to arrive (extract from "purchase book master") the qty ,lot ,and landed cost /unit as on thedate of closing balance which is in sheet"closing balance FIFO" incell A1 onwards.Assume the consumption occured on FIFO basis.SO the closingbalance lying is from latest one and the previous one if qty is not sufficientto cover closing balance.

Theoutput desired clearly indicates the solution I am expecting.What I havepresented is only a small portion of data.So I need someones help to sove this issue.

TOBE system I am looking for.
1) User will place the file (to be uploaded) in a specific folder
Constraint - File may have different name each time, but there will be only one file in the folder at a given time.
2) Macro should read data from this file and upload into the system

Problem facing - Using Transfersheet functionality but since my file name is different every time, the macro is not able to find the file. How can I overcome this problem and make macro to pick file with file name consideration.

Ideally I want the system to work in a way that any time someone drops file into this folder, system should read the file automatically (FIFO) and then move this file to some other folder and picks the next one in line. Is it possible?

any pointers will help

With our inventory, we want to use the product that has been sitting first (FIFO - First In First Out). I have two worksheets:

1) Data entry - which is the product being sold/shipped
2) Purchased - which is where we put the new material coming in, price and what not...

I am trying to figure out the formula for looking up what was sold/shipped in the Data Entry worksheet and have that take from the inventory purchased first in the purchased worksheet.

Attached is the worksheet. I appreciate any help on this.


Dear Sir,
please help me for calculation of capital gain through stocks on FIFO Method . i had attch my stock trade book file for your reference. it will be very grateful as well as very helpful for me if you help me by adding any formula or any macro function which will calculate capital gain for different stocks base on FIFO method. in my attach file i had attch CP WORK SHEET which i had downloaded from boker website.
so the program should not only calculate the profit by subtracting the cost of shares on FIFO basis (that is identify the earliest existing stock). but since the data does not readily give me the correlation between the purchases and sales on FIFO basis

i want to know how CP calculation base on FIFO Mehod by first considering sale qt, sale date, sale price then purchase qt base on sale qt, purchase date, will be done in excel file base on Trade book data.

I had uploaded file in discussion forum.

Thanks a lot in advance



Hoi, ik wil een excelsysteem maken in het format zoals ik in de bijlage heb meegestuurd. Ik loop echter tegen wat problemen aan waarvan ik hoop dat jullie die voor mij op kunnen lossen (eventueel met gebruik van VBA). Zo moet automatisch het juiste materiaal gevonden worden en moet de juiste prijs berekend worden bij het gebruik. (zie excel). Een bijlage met omschrijving is te vinden op:

Wie kan mij helpen?

Groeten, Excelhenk.

Hello All ,
I need the FIFO formula to create a new spreadsheet in my excel book , anyone can help to solve this ??
Thanks a lot !images.jpg

Hello Excel Forum ,
I need a formula for my worksheet & stock control for the below image , anyone can help me to solved this .
Thanks a lot !

Hi All,

I need help to come up with a solution for FIFO (First In First Out) method for tracking the "buys" and "sells" in a list of stocks. I would prefer a solution without UDF or VB programming (Im not good with those). Just a clever Excel setup/formulas would help tons.


Bond Trans Date Amount
A Buy 1/1/2010 1,000.00
A Buy 1/15/2010 1,000.00
A Sell 1/20/2010 (1,000.00)
A Buy 1/25/2010 1,000.00
A Sell 1/30/2010 (2,000.00)
B Buy 2/1/2010 500.00
B Buy 2/5/2010 300.00
B Sell 2/15/2010 (300.00)
B Sell 2/27/2010 (500.00)
C Buy 3/6/2010 250.00
C Sell 3/10/2010 (250.00)
C Buy 5/2/2010 400.00
C Sell 5/24/2010 (400.00)

So, basically I need to apply the first "Sell" date to the first "Buy" date. Stock A was first sold on 1/20/2010 for $1,000, therefore, I would reduce the first "Buy" on 1/1/2010 by the 1,000. This is FIFO method. The raw data comes in this format and we can change/adapt the layout to get FIFO working.

Thanks for your help!!!

I am building an inventry program which involves both the amount of item in stock and thier value.

Does any one know how to do a FIFO ( first in-first out) formula.
I appreceate any help, thanks