.

.

Hello,

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

Thanks in advance for your help.

regards,

Pedro

.

.

.

Hello,

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

Thanks in advance for your help.

regards,

Pedro

.

.

- inventory data needing running average costs
- Calculate Weighted Average Costs
- How many items per month and average
- Grouping and averaging
- Need formula to reference multiple cells, and average.
- How to calculate the average cost of a particular category from so many models
- Average Costs for desired data
- Even Distribution of Units Across all Prices
- Setting and averaging changing ranges from another sheet
- Average two cells, skip n number of rows and average again
- Aggregate and compile data from other sheets
- Sum and average certain columns and paste
- Making average buy price and average sell price
- Insert new rows and averaging data
- How do I extract 3 middle results and average them in excel
- COUNTIF and AVERAGE only cells in unhidden rows
- Can you calculate "weighted average cost of capital?
- Sum and average number of days and hours
- Sum and average based on month and year
- Using both sum and average in subtotals
- Newbie - Help with Counting and Averaging Dates
- Macro- delete 2 adjacent rows, insert counta and average functions of selection
- Sumproduct and Average Problem
- Can you calculate &quot;weighted average cost of capital?

I have an inventory (attached) and I would like to know how to attach a fill-in-the-blank form to buy/sell/use items from the inventory. this form will also change the quantity in the data inventory worksheet along with the average unit cost, and total unit cost. Can you great people help me. I am reading the VBA info but it is going to take alot of time. I would like training/tutorial in near future.

VB:Single 'STR1 identifies the specific item [a refrigerator] 'RNG1 identifies the Description column [refrigerators, stoves, supply lines, everything] 'RNG2 identifies the Transaction column [Purchase, TransferIN, TransferOUT, Installed, Sell / Dispose] 'RNG3 identifies the IndexB column [this is a calculated field reflecting total cost (QTY * Price)] 'RNG4 identifies the QTY column [this is an input field reflecting the quantity of each transaction] 'RNG5 identifies the current row - could be pulled from STR1 if dimensioned as range [identifies current row] 'iSheets identifies the current sheet index - used "with / end with block" [identifies current sheet] Application.Volatile Dim iRNG1Col As Integer, iRNG2Col As Integer, iRNG3Col As Integer, iRNG4Col As Integer Dim iStart As Integer, iStop As Integer, iSum As Single, iCnt As Integer Dim rCell As Range With Sheets(iSheets) iRNG1Col = RNG1.Column iRNG2Col = RNG2.Column iRNG3Col = RNG3.Column iRNG4Col = RNG4.Column iSum = 0 iCnt = 0 iStart = Range("DetailBeg").Row iStop = RNG5.Row For Each rCell In RNG1 If rCell.Value = STR1 Then Select Case rCell.Offset(0, iRNG2Col - iRNG1Col).Value Case "TransferIN", "Purchase" iSum = iSum + rCell.Offset(0, iRNG3Col - iRNG1Col).Value iCnt = iCnt + rCell.Offset(0, iRNG4Col - iRNG1Col).Value Case "TransferOUT", "Installed", "Sell / Dispose" iSum = iSum - rCell.Offset(0, iRNG3Col - iRNG1Col).Value iCnt = iCnt - rCell.Offset(0, iRNG4Col - iRNG1Col).Value Case Else iSum = "#N/A" End Select End If iStart = iStart + 1 If iStart >= iStop Then Exit For End If Next End With PRICESUM = iSum / iCnt End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).

One “reporting group” has many cost centres.

There are several reporting groups.

I need to report on average salary per grade / per reporting group.

I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.

In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.

I hope this is clear, thanks for looking!!

I'm not even sure what formula to begin with, whether it's =if, some form of =vlookup, i have no idea. So any help would be very appreciated!

What i need to do is have it so cell B4 in the Reference Tab (Week 1 for 2011), looks at the Weight tab, and finds both 2011 and Week 1 in the same row for everything that applies (in this case, i need it to look at cells A172:F175), add everything that applies from the F column, and divides it by everything that applies in the E column.

So I need a formula in B4 in the Reference tab that basically says:

"Find everything that applies to the week on my left, for the year above in the Weight Tab, and average weight for that month and week"

I'm not sure how to word that better, but if i can explain things better or answer any questions, i'll happily do what i can.

The second formula i need (also in the Reference tab) would go into i4, i'd imagine this is damn near the same formula, if any differences at all. Similar to the one above, i need it to look at what week and year it applies to, then looks at the "Costs" tab (in this case, A172:F175). Sums "Expense", and divides it by the sum of "Total Profit" for that specific Week and Year.

I'm looking to find a way to do this as automated as possible as this is part of a very thorough daily running report, but i'm looking to avoid VBA and Macros as much as possible since it's a large corporation where using either of these have been disabled on 90% of computers other than about 4 people in my office. Any help is more than appreciated, thank you!

Please refer attached file for more clarification.

Regards

Pradeep Kumar Gupta

I think this is a simple question, but still I am messing up the formula. Hope you guys can help me out. I will truly appreciate for your help.

I have column A with a given list of City Pairs, and the costs associated are listed in Column C> NOTE that Column A can have multiple same City Pairs, for example in Column A, Row 3 to Row 9 have same city pair ATL to DFW (listed as ATLDFW). and now coming to the main problem, I have in column E desired set of city pairs that I want the average of costs among the given set of pairs (Column A).

in order for it make sense I have attached a copy of the excel spreadsheet that i have been working on.

Please help me with this issue guys.Help 2.xlsx

Refer to the attached sample workbook,

(Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)

Cell F19 should contain =D19*B19

Column 1- These values will not change and represent price/cost of each UNIT

Column 2- "UNITS" the quantity or number of items at column1 value

Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2

Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed

Ideally speaking, As few units in one row as possible to allow for a good mix.

For example: What i don't want is $9000*86 for 774k.

If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.

I believe a Most Even distribution of units will work fine.

After the distribution/population of fields in column2;

D29 must equal F3

F29 must equal F7

I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.

The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.

I have a spreadsheet with lots of batches, but the example I am attaching has only 2 batches.

Now my problem is, I have a function that determines when the GRIND TIME starts and ends in this batch. So, what I am looking for is capturing the average temperature of a chemical during Grind Time.

I am having trouble telling Excel to average a group of number between two times.

For Example, in the worksheet, you will notice that

Batch "B0401" has a Prod Time of 7:12 (sheet3) and a Grind Time of 7:52

Batch "B0402" has a Prod Time of 9:51 (sheet3) and a Grind Time of 10:51

I need to find these times, based on the batches (Lot ID) in sheet4 and average the "mixer 2 water flow" during this time only. This average should go into the "chill water flow ave" column in sheet3

I have a large number of batches for each month in many worksheets, so a function that I can drop and drag or a vb code would be great.

Clear as mud? Thanx for trying!

mctld

I have a very large data set in which specific cells need to be averaged. The cells that should be averaged are in a predictable locations relative to one another.

For instance, Cells A3 and A14 need to be averaged, and then cells A25 and A36, and then A47 and A58 and so up to A400 or so. My current formula will average A3,A14, but then averages A14 with A25 which is not what I want.

Do any formulas come to mind to solve this? Perhaps averaging the original cell and 11 after it, skip 11 cells and average that cell and 11 after, etc...

I am not sure how to accomplish this. Anything pointing me in the right direction would be much appreciated! Please let me know if the original .xlsx file is need.

Thanks again.

I have a mechanism in which I enter the details of a trade (whether I have bought or sold a security) into cells D5:D14 in the 'Trade Blotter' worksheet and when I press 'Add to Portfolio' (in cells A1:B2), each trade is subsequently moved to a list starting in row 25. The aim of this is to keep a historical track record of every security I have ever bought or sold.

What I am looking to do is to organise these individual trades by security on 'Sheet 3', so that whenever I add another trade into the 'trade blotter' tab, it automatically updates the information on 'Sheet 3'. So for example, if there have been 2 trades with the security ticker BER historically on the 'trade blotter' sheet (which can become thousands of entries), it will show the aggregate values of these 2 trades (i.e. how much they cost total, how many shares were bought total, etc) and display these details in 'Sheet 3'.

The information I am looking to aggregate for each security is in the 'Trade Blotter' worksheet by individual trade. The information I need to aggregate is the total shares owned (formula shown in cell B2 'Sheet 3'), total cost (formula shown in cell C2 'Sheet 3') and average cost per unit (formula in cell D2 'Sheet 3').

There will be thousands of trades over time and many different securities, so the goal is to have 'Sheet 3' become a summary sheet that can tell me at any moment, how many shares are owned of each security, the total cost and the average cost. As the securities are not known in advance, I will not have every single security listed in column A of Row 3, thus it is important that where it is the first occurence of a security in 'Trade Blotter', that security is automatically inputted in column A of 'Sheet 3' (this is where I believe VBA would be required).

I have spent the past week at about 4 hours a day doing this and cannot figure out how to achieve this, so I appreciate any help that can be given.

I'm looking to use a command button which upon a click, ask the user which years from the 15 years of columns I have to sum and average.

So I imagine it would be like click the comm button,

1st question, which years would you like to sum (the years could be seperated by a comma.. because of the 15 years where each column represents each year, a user might want year 1 to 3, than 5 to 7).

I imagine would have to use the special tool, like when you chart a number of rows, than seperate with comma... where you highlight a section or range, than seperate by comma and highlight another section of a range of rows..?

Or without highlighting physically the range, we enter the years into the input box, with a comma...

or another way is when a commnand button is clicked, a userform with 15 radio box is shown, each radio box signifcying each year of the columns, and when you check it it signifies the years to add and average.

problem is also, i have like say 15 rows of line items, don't want to create a userform for each one, so the userform would or should have a dropdown box that reads the actual rows to designate

for example

Line Items (col 1) Year 1 (col2) Year 2 (col3) ... Year 15 (col16) Sum/aver (col17)

Revenue

Expenses

COGS

From the above, once we select the years would sum the chosen years and divide by the no. of years chosen and dump this into col 17 for each of the respecdtive line items.

any help appreciated.

This is too complicated for me, so i am asking anybody's help to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work. Please help.

Sub HourlyDataSum()

'

' HourlyData Macro

'

'

Const xlShiftDown = -4121

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:Scripts4-9 to 4-17 Weather Data-1")

Set objWorksheet = objWorkbook.Worksheets(1)

i = 1

j = 1

Do Until objWorksheet.Cells(i, 1) = ""

If j = 5 Then

Set objRange = objWorksheet.Cells(i, 1).EntireRow

objRange.Insert (xlShiftDown)

strFormula = "=average(A" & i - 1 & ":A" & i - 4 & ")"

objWorksheet.Cells(i, 1) = strFormula

objWorksheet.Cells(i, 1).Interior.ColorIndex = 44

j = 0

End If

j = j + 1

i = i + 1

Loop

If j 0 Then

Set objRange = objWorksheet.Cells(i, 1).EntireRow

objRange.Insert (xlShiftDown)

strFormula = "=average(A" & i - 1 & ":A" & i - j + 1 & ")"

objWorksheet.Cells(i, 1) = strFormula

objWorksheet.Cells(i, 1).Interior.ColorIndex = 44

End If

End Sub

Any help would be greatly appreciated.

highest and the lowest figures and average the other three to get a result.

How do I do this. I have tried

=IF(H71B74,B75,B76,B77,"0","H71")

h71 IS THE CELL CONTAINING THE SCORE WHICH SHOWS IN B73. THE OTHER FOUR

SCORES ARE ALL SHOWN IN B74, B75,B76 AND B77. i ONLY WANT THE MIDDLE THREE

SCORES TO BE AVERAGED AND PRODUCE A RESULT. HELP WOULD BE MOST APPRECIATED.

times, 12/18/08, 20:00. I subtract the two columns to get the number of days

and hours between the two times, which is formatted with a "d:h" custom

format to show the rounded off number of days and hours, 10 D 5 H. This

column of time differences needs to be added and averaged. The sum function

does not show a correct total nor dos the average function show a correct

average. Is it a function issue or a formatting issue? The total for the

hours and the average of the time differences should be in a day hours

format, d:h, or some format that will show the total or average days and

hours. I have experimented with a varity of formats and tried different

functions but have been so far unsuccessful. Any assistance would be

appreciated. Thank you.

Basically I'm trying to sum and average values based on what month and year they are, with any date.

I've been trying to do something like this:

VB:but it doesn't seem to work...=SUMIF(A3:A81,"="&DATE(2009,10,"*"),B3:B81)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

also, same question, but with an averageif twist?

I'm very new to excel VBA programming..can any body please suggest a

easy solution for the following:

I need to use both sum and average in subtotal function on different

columns at a time..

eg: Column A : Name

Column E: Rate

Column F: net value

I want to do sum on Column F and average on Column E for every change

in column A...

Any help will be greatly appreciated..

Thanks..

I have a spreadsheet where I have employees create ticket open and close data. They have their own sheets so I copy paste their info into a template. Then, I have to delete the two rows of data (not the actual rows themselves) under what I just copy/pasted, then enter a count and average label down 1 +2 rows and over 4 columns from the bottom of my selection. Then I need counta and average down 1 + 2 rows and over 3 columns from the bottom of my selection. I'm selecting data from the right most set of data. I've pasted the record macro so you can see what I'm trying to do.

autoall Macro

' Macro recorded 11/1/2010 by IBM_USER

'

' Keyboard Shortcut: Ctrl+o

'

Rows("10:11").Select

Selection.ClearContents

Range("H10").Select

ActiveCell.FormulaR1C1 = "Count"

Range("H11").Select

ActiveCell.FormulaR1C1 = "Average"

Range("I10").Select

ActiveCell.FormulaR1C1 = "=COUNTA(R[-7]C[2]:R[-1]C[2])"

Range("I11").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-8]C[2]:R[-2]C[2])"

Range("H10:I11").Select

Selection.Font.Bold = True

Selection.Font.ColorIndex = 5

End Sub

I have a simple little sumproduct formula that is supposed to calculate and average, however it doesn't work they way I need it to the reason is BLANK cells

=((SUMPRODUCT(--(tableno=$A3),D$66:D$5485)))/SUMPRODUCT(--(tableno=$A3)).

Below is a small extract, so A3=21, the first part of the sumpoduct gives me the correct answer of 14,156, the second part also works and give me the answer 5, but what it's doing is working the average of 14,156 / 5 = 2,831.20 which is not what I want, I need it to ignore where 21 = Blank and give me the true average of: 14,156 / 3 = 4,718.66.

tableno. D$

21 4,974

21 4,390

21 4,792

21

21

Because that tableno is used for many other calculations in the same spreadsheet I can't just simply delete the 21 from the blank cells. I need some sort of way to say if(D$="" - also do not count tableno...

Does that make any sense?

Thanks

No luck finding an answer? You could always try Google.