# FIFO / LIFO And Average Formula

Jun 15, 2009Need fifo, lifo and average formula ....

Need fifo, lifo and average formula ....

I need to compare and calculate the Unit Cost Price of my Inventory based on the 3 methods of inventory valuation: FIFO (First In, First Out), LIFO (Last In, First Out) and Average Cost.

Next, I enclose 3 snapshots of each method with the results required (columns color yellow).

I have inventory... with starting product at a certain cost, received product at a new cost, and used product. I want to assume that we are using FIFO.

What I need is a total cost (what I have paid) for what is sitting in my freezer.

Column A is START (5)

Column B is RECEIVED (6)

Column C is USED (2)

Column D is END (9) or (A1+B1-C1)

Column E is OLD COST ($12.20) cost per unit of those 5

Column F is NEW COST ($13.50) cost per unit of the 6 i got in

So I need in Column G a FIFO formula for total cost of what I have in the fridge.

I have a worksheet containing data for a product my company manufactures. I want to make a userform with 1 combobox and 4 textboxes for first in first out management of inventory. From the columns in the worksheet the combobox = "Product", textbox1= "Container", textbox2= "Production Date", textbox3= "Warehouse Location", and finally textbox4= "Sheduled Ship Date".

I need it to work by the user selecting a particular product in the Combobox, based on that the first 3 textboxes are populated based on the oldest production date for that particualr "Product". The user can then input into the 4th textbox "Schedule Ship Date" the appropriate date, then hit a command button to update the spreadsheet with the "Scheduled Shipping Date" ...

I have a workbook with a series of #'d tabs corresponding to different pieces of inventory tracked independently (I've simplified it significantly and attached it). I would like to use the First-in First-out inventory valuation method to calculate the current cost of inventory. A manual calculation and description of the method and the proper result is included in the sample file.

I have come across numerous references to http://www.cpa911.com/read_article.asp?ID=46 here, via google, other messageboards, etc; however, when I have tried implementing it, even as described, it never seems to work! (Note: I've included the code and set it up per their instructions in the attached file...of course, unless I misinterpreted their instructions and my problem lies there...) Moreover, I'm not sure how I would implement this particular macro anyways using named regions considering the same macro would have to be run across several sheets. As a result, the named ranges would always pull data from only the one sheet containing the named range required by the macro . This begs the question of whether there a way to create unique named ranges for each sheet that would be interpreted correctly by a single macro running across multiple sheets...

If this is at all unclear, I would be more than happy to rephrase any/all parts. After all, this problem has been aggravating me for weeks, and I am at the mercy of this forum's gifted coders who may not be well versed in accounting theory.

I have a file where I want to make a report which will put the stocks having positive balances along with their prices and values.

Stocks are to be valued on FIFO (First in first out) method ie when the stocks are sold it means it were sold from the first lot bought and so on.

Reporting is required for Shares Held For eg. for Stock A, Buy qtn is (50+50+100++100) and sell qtn is 100 So total balance is Buy Qtn 300 - sell Qtn 100 = 200 Sell Qtn 100 will out of first 50 & then next 50 (First in so first out) balance held in hand is out of last two Qtn 100 purchases. The prices are corresponding to these two lots of 100 stocks.

Reporting will be for Stock held (lotwise) See Sheet 2 of the file attached.

Basis of price will be fixed ie no formula etc and corresponding to those lots.

Actually these data are copied from web qwery so formula etc are not required. For cost purpose, price and Total correponding the balnce qtn lot are relevant

In case more clarification required, do let me know. I will try to make them clear.....

I need help to calculate inventory value using FIFO method...

View 9 Replies View RelatedI'm using the formula =AVERAGE(B16:L16) to give me the average.

However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.

Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.

I am trying to create a worksheet that tracks the age o f the inventory in my warehouse. I am charged each week at an increasing rate and want to be able to track what these charges will be. I would like to be able to sum up the data below in a pivot table that shows how many units are falling into each age group, this would add up to a max o f 10 weeks o f increasing charges.

3/1/14 received 1000 units

3/6/14 ship 600 units

3/8/14 charge for 1 week at 400 units

3/8/14 receive 500 units (900 pieces on units)

3/13/14 ship 300 units

3/15/14 charged for 100 units at 2 weeks and 500 units at 1 week

This needs to be able to assume this is going on for 10 different items, I would like to be able to track the items independently and in bulk.

Make an interactive calendar in excel. I would like to have 3 sheets. One were I can enter the names and corresponding reoccurring roster, that would be days on days off, a second sheet that does all the calcs etc and returns days of which all or most of the people are going to have off at the same time, so we can organise things in advance and maybe a third sheet that shows an actual graphical display of this information like a traditional calendar.

View 2 Replies View RelatedI'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.

I have a number of equity trades (both purchase and sales). I need to know the book cost of those sale trades to figure out the realized gain/loss according to First-in-first-out method.

Since the unit sold may included units bought at different time and different price, so I have to first exhaust the first lot that I purchase before moving into second lot, and so on and so for.

Column G - J are for illustration. I highlighted K10, K11 and K12 are book cost that I want to calculate. Instead of manually separating out units in each lot previously bought. Is there an automatic way? I would not mind adding new columns to ease calculation.

Cell: F6 is to represent "average weekly offering".

D2:D53: represents the Total Given Year To Date.

I need a Weekly Average for Offering, so is it possible to have a formula that can show the average weekly offering in cell F6?

I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.

Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.

What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.

I'm building a yearly workbook with multiple worksheets (i.e. one for each month, quarterly reports and one final yearly report). My question is that although I can have the data transfer easily to my quarterly and year end report sheets, I can't seem to figure out how to filter my data and then calculate an average based on personnel averages in the various categories I have set up.

My example is that I want to be able filter my personnel shifts (i.e. 7am, 3pm, 11pm, etc) and then set up a formula which can for example calculate a column of data and average it only based on the employees working that particular shift. My formula should adjust for the shift time I pick.

The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.

Then, I need the following logic as VB macro.

for row = 6

F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")

I have a large amount of data in 2 columns which I want to statistically average to see if a relationship exists between two variables.

Column A: Contains Iodine concentrations ranging between 0 and 22000 ppm.

Column B: Contains Nitrate (NO3) concentrations ranging between 0 and 22 %.

I have over 7500 rows of data. In theory there is an endless amount of data but it will all be kept in these two columns

What I want to do is create average values of NO3 based on set Iodine ranges.

e.g. I want to know the average NO3 grade for the cells where the Iodine concentration is between 0 and 99 ppm.

I want to be able to calculate this average based initially on 100ppm Iodine bins but dont know what clause I would need to add into the formula or if this is even possible.

I would also like to be able to edit the bin size to see if I can discern a better relationship between the two using different ranges. As such I can create the bins in a separate column but again I don't know the best way to do this to make it work well in a formula.

I have data regarding the percentage of QC for the team.

I need to find out the individual's average QC scores from the list already available.

The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage

I am trying to average a data set where some rows at the moment are blank but will update at a later date, I am getting a div/0 error, the formula I am trying to use is

=IF(C23:C30="","",AVERAGE(C23:C30))

I thought this would look at the data and see if its blank leave it blank but if there are numbers there it would give me an average.

I am trying to do a stock ( goods) inventry for a catering industry, meaning most good swill keep havinf cost prices changing.

I am trying to use a LIFO system. To achieve the end result, i am trying an average formula and i am trying something as follows:

=SUMIF(I4:I20,">0",I4:I20)/COUNTIF(I4:I20,">0")

To try it out, i created a similar page with the same amounts but using a normal multiplication and division formula, the totals dont marry.

I am working on formula to return an average of data.

Currently it is matching a text criteria.

Thus if (the text in) column a = (the text in) column b, (return the average of) column c.

The formula that I am using is =IF(A:A,B:B,AVERAGE(P:P))

This is returning - #value!

Now is this a formatting problem in column P? Or is the formula I am using incorrect?

I know that the text criteria (col A & B) matches.

I am trying to do an average of quartely results (2008 results) and because certain quarters can have no data present then my quarter shows 0%. But if i do a simple average of all Qs then the 0% affects my overall year to date. I need to keep the 0% because it may be a legit value. So i've devised a way to is if we actually have something in the cell (using isblank). So i test with isblank and if something is present then i use the Quartely value. It could be 50% or it can be 0%.

I would like to incorporate the snapshot but i can't because i do not have access to uploading a picture. If any of you wish i could email the pictures. When i test out my formula in different cells its works but when i combine them all together i get #VALUE! error.

In the example the error comes from the Q3 which test out and should give "" but when its added to the formula i gert #VALUE!. Shouldn't the average function disregard "" cells?

My formula is:

=average((if(and(isblank(F33),isblank(J33),isblank(N33))," ",R33)),(if(and(isblank(T33),isblank(X33),isblank(AB33)),"",AF33)),(if(and(isblank(AH33),isblank(AL33),isblank(AP33)),"",AT33)),(if(and(isblank(AV33),isblank(AZ33),isblank(BD33)),"",BH33)))

Maybe my logic is flawed or there is a better way to do it. Either way i would like your opinion on it.

I need to count the daily average of a task to a week ending number.

I need to see the current average after each day during the week. Example Mon = 2, Tues = 4 AVERAGE is 3 Wed = 2 AVERAGE IS NOW 2.6
and so on averaging out after each day is added.

Looking for an average formula.

All found formulas average within a string (ex. A1:F1).

My spreadsheet contains a series of three columns.

Example:

Column A reflects a number stat for a month (January).

Column B reflects another number stat for the same month (January)

Column C reflects the difference between Columns A & B.

Column D reflects a number stat for the next month (February)

Column E reflects another number stat for the same month (February)

Column F reflects the difference between Columns D & E.

and so on.

I would like to average the rows stats reflected in Column A, D, G, J, and so on.

Also, I would like to average the rows stats reflected in Column B, E, H, K, and so on.

Also, I would like to average the rows stats reflected in Column C, F, I, L, and so on.

There are (i think) 5 t-ball coaches

25 5 yrs old

44 6 yrs old

each were score on RUN, THROW, GROUND BALL, FLY BALL, BATTING,

scores were: 5-Excellent, 4-Very Good, 3-Good, 2-Average, 1-Fair

So I was thinking of making 5 sheets (one for each coach, with their scoring for each players)

The last sheet will be what the Average would be for each area from each coach, giving me a general idea about the players that I didn't get to watch and score.

So how would I formula the Avarage sheet?

Again I'm on short notice, and need to have this done on Sunday, when the information starts to come in and I can data-entry the scores, and have it ready for Monday nights draft.

I have a database of information for which I need to be able to calculate the average of the top 20% for each column for all those rows that meet a certain criteria. In other words, I need a formula that's equivalent to the DAVERAGE function but I only want it to work on the top 20% of records that meet the criteria range that I've specified (as opposed to DAVERAGE which works on 100% of records that meet the criteria range). The database continues to grow so I'm unable to predetermine what quantity makes up this top 20%. I've attached a small sample file to show you what I'm talking about.

View 5 Replies View RelatedTrying to average multiple cells that are not consecutive and contain zeros that i want to disregard. I have tried to do an array formula but the cells must be consecutive so I am at a loss. This is what i tried. =AVERAGE(IF(A2,A8,A12<>0,A2,A8,A12,))

View 9 Replies View RelatedHTML Code:Β

=100*($I2/AVERAGE(IF($B2=$B$2:$B$38129,$I$2:$I$38129)))

I have the above array formula which I wanted it to calculate the average of all products selling in each store, but it seems to be calculated each?

In column b I have the store number, column I is the sales, I want to average all the sales in column I for each store.

I have probably over complicated?

What I am trying to do is rank my employees based on certain metrics. They are weighted in three different catagories. Talk time is 40%, Quality 50%, Attendance is 10%. I can weight the Talk Time and Quality, as those are basically the higher the score the better the weight. Attendance is the opposite, the lower the number, the higher the weight. Meaning, if employee has 1 absence that would better than someone who is at 4 absences. Is there a way to do this. I am somewhat educated on VBA, I understand if,then,else statements and/or Select/Case statements, so if I needed to do it in VBA I can. I just need direction to get it going.

View 3 Replies View RelatedI am trying to create a formula that looks for the last populated cell, counts 12 cells back and returns an average for all of the cells in that range. I know I have done this before with some combination of INDEX and COUNT, but I can't seem to remember how I did it and my experimenting is not proving fruitful.

Attached is a simplified version of the layout I am working with

I'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.

View 8 Replies View RelatedI have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using. On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.

So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at. I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.

Im having a problem finding a formula that will count and take an average of multipul ifs.

On sheet 1 of the attached file is multipul queries raised for us to answer.

On sheet 2 is an empty table which i would like help formulating.

In the Submission Lag Time Column i would like a forumla that looks down Column L on Sheet 1 and gives me an average for the amount of days a query has been with us from each company (column B) per month (going off the dates in column D).

I the same formula will be used for the other 3 columns, put looking down a different column on sheet 1.

How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE($D$2:$AH$2)-C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE($E$2:$AH$2)-C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?

See Attached.

The YTD 10 column is fine that will change everytime you update each month. I was wondering if there was a formula I could use for the Rolling 12 month Column.

At the moment it is averging from Mar 09-Feb10 which is correct but when it comes to putting in data for Mar 10, the Rolling 12 month column will obviously not change.

Is there a way of moving the formula along one each time you input data into a new month. So when Mar 10 has been entered the rolling 12 month column will pick up Apr-09-Mar-10 then when Apr 10 has been entered the formula will change again to May-09-APr-10?

See attached picture. This shows Profit and Growth for the stores in each region.

The percentage total is weighted based on the profit of each store in the region.

What I have is an excel sheet with hundreds of regions (going down), all with a different amount of stores. I need a way to populate each of its Region % Total with the weighted average formula (in my example, cells C8, C12, and C20) while also varying the formula to include only the stores in the region.

For example, C8 calculates the weighted average for %s in rows 2 through 7 (6 stores), but C12 calculates it for rows 9 through 11 (3 stores). I need the formula to vary based on the number of stores.

I'm trying to get a formula working that is basically for a rating chart. The chart is somewhat simple, but the formula is a bit complex (for me anyway)

So the details are the rating is on three axis with three levels, and here is what I need to do.

At the moment the formula below works, but it isn't 100% accurate because I actually need to take an average of F5+G5 then take that value and add it to H5 then divide by two to get the proper average of the two charts. (if that makes sense.

=IF(AVERAGE(F5:H5)>2.33,"High",IF(AVERAGE(F5:H5)>1.67,"Medium",IF(AVERAGE(F5:H5)<1.67,"Low")))

Here is the formula that I thought would have worked, but Excel begs to differ

=IF(SUM((AVERAGE(F5:G5))+H5)/2>2.33,"High",IF(SUM((AVERAGE(F5:G5))+H5)/2>1.67,"Medium",IF(SUM((AVERAGE(F5:G5))+H5)/2<1.67,"Low")))

I played with the brackets quite a bit thinking I had something in the wrong order, but nothing seems to work. I always get FALSE in the field and an error as well when I enter the formula.

In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead.

View 13 Replies View RelatedIs there a way to include an if then else function to an average formula that will skip cells with zero?

I went into better detail in the attached example.

I have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4...

View 9 Replies View RelatedI am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.

For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.

What would the formula be to get the correct average time?

ADVERTISEMENT