Sum The Total And Find The Average Cost
Oct 1, 2008
I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price ($) together and divide by the sum of A&B.
In other words find the average price for the total of each item..
A B C
Code Qty $
PH06003000 1 1504.8
PH06003000 1 1582.24
PH06003000 1 1606
PH06003000 1 1504.8
PH06003000 2 3009.6
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06024000 1 2499.2
PH06024000 1 2499.2
PH06024000 1 1896.07
PH06024000 2 3909.66
PH06024000 1 2240.7
PH06024000 1 2259.4
PH06024000 15 30030
PH06024070 1 2039.4
PH06024070 1 1958.66
PH06025670 1 2521.2
View 9 Replies
ADVERTISEMENT
Mar 15, 2007
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
View 5 Replies
View Related
Dec 17, 2009
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies
View Related
Aug 6, 2009
I need to calculate the total cost of outbound calls based on the total duration of outbound calls multiplied by cost per minute. For example, in a given month, the total duration of outbound calls is 261:16:34 being 216 hours, 16 minutes and 34 seconds. I have this figure in cell A1 with the format [h]:mm:ss. I then convert this to minutes in cell B1 by saying B1=A1, but having the format [m], which gives me 15676. In cell C1, I have the cost per minte value of £0.026. But when I apply the formula D1=B1*C1, I get £0.283, when 15676*£0.026 should in fact be £407.58.
View 2 Replies
View Related
Jan 26, 2014
I am trying to keep a running total of cost, here is what I am doing
A B C D E F G H I
Date Books Pencils tax Month Books Pencils Tax Total
1/2/2014 11.50 2.50 .76 January 35.85 13.50 3.44 52.79
1/5/2014 14.50 3.85 .83 February 10.95 1.50 .68 13.13
1/6/2014 9.85 7.15 1.85
2/6/2014 10.95 1.50 .68
I only used Jan and Feb as an example. So as you see on the right side I will have all 12 months for the year and I just want all the totals for each month to auto calculate. I will have between 15 and 30 entries each month.
View 5 Replies
View Related
Oct 19, 2006
Example number 1:
You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms
Example number 2:
In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years.
How big will the difference be between example 1 and 2 ?
*** Background.
If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect.
Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.
View 2 Replies
View Related
Aug 5, 2014
Pivot_example.jpg
Regarding the attached pivot table screen shot, I would like to be able to add total revenue and cost for each product and overall to this pivot table. IOW, instead of just showing the net amount of 3,200 for all blenders, I would like it to show Revenue of 12,500, Cost of (9,300), and the net amount of 3,200 for all blenders (i.e., for Boise and Chicago combined), in addition to the totals for each of the two plants. Also would like to see total revenue, cost, and net for all products and plants combined at the bottom. Can this be done?
View 4 Replies
View Related
Dec 22, 2013
I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.
I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?
sheets.time chart.xlsx
2014 tab- grey column is installer and white is shop employees
profit loss tab- all calculations done here
payroll tab- all cost data here
View 1 Replies
View Related
Oct 14, 2005
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).
View 9 Replies
View Related
Jun 10, 2014
Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.
Please see attached file : Formula.xlsx‎
View 1 Replies
View Related
Sep 24, 2009
I have been assigned a task of finding the lowest cost of four possible solutions however I have quite an extensive list of items to work with. To make this easier, I need to be able to find the lowest cost in my row (which is not sorted by lowest to highest value) and return the column heading associated with that lowest cost.
View 2 Replies
View Related
Jul 28, 2014
I am trying to combine sub total if the formula matches a certain criteria.
I would like an average of the data I have previously used an average if formula =averageif(D:D,L4,J:J) but now I would like the answer when I filter the data.
View 4 Replies
View Related
Feb 26, 2008
=SUMPRODUCT(--($A$2:$A$12=$D$24),--($F$2:$F$12=$B$25),($H$2:$H$12))
This is the current formula I have, however I am working with averages. After the first two arguements are done I want the overall average of columun H.
View 9 Replies
View Related
Mar 26, 2009
I created a spreadsheet to track my bowling scores by date by lane
Date Lane game 1 game 2 game 3 total
What I would like to do is show my total average by lane for the season. 33 weeks. From there I think I can graph it out.
View 4 Replies
View Related
Mar 26, 2014
I have a workbook with each month as a tab Jan 2014- dec 2014. I have a totals page that's has total billings( the sum is adding all the totals of each month). The totals for each month are in different cells based on the number of individual invoices I enter for each month. I have entered jan- march invoices. I would like to put in a formula on my totals sheet that gives me a ytd avg without changing it. ie: d4/3 then next month april d4/4.
View 1 Replies
View Related
Apr 10, 2014
On a pivot table, I want the grand total to be the average. When I code it, the code changes all the values in the column to an average.
if a person's % for April is 95% & the sum of all the people in the table is 1924%. If I change the grand total to average, the person's april % changes to 19% (which is an average instead of a total.
[Code] .....
View 1 Replies
View Related
Apr 1, 2013
I have a team of 8 people who take calls. I need to get the median (and average) value of the total number of calls for these people for a specified date range, say 1/1/2013 to 1/24/2013.
When i use the following f(x):
{=MEDIAN(IF((Call_Data!$A$2:$A$13000>=$E$8)*(Call_Data!$A$2:$A$13000
View 1 Replies
View Related
Nov 23, 2013
I am trying to get and average price for a total quantity of jacks
Material Price row G cells 4 - 56
Material Labor Price row I cells 4 - 56
Jack Quantity row L cells 4 - 9
I had this =SUM(G4:56)+SUM(I4:56)/SUM(L4:9) just wont work?
View 5 Replies
View Related
Feb 11, 2013
I have a column in my pivot table with values that are formatted as percents. I am trying to make the grand total reflect the average of all values in the column, but it keeps showing a sum of all values.
Example: the values in the % column are 90%, 100%, and 110%. I want it to show 100% (the average), but it is showing 300% (the sum)
View 1 Replies
View Related
Nov 6, 2013
Capture an average from the Grand Totals in a Pivot Table? If so what are the steps.
View 5 Replies
View Related
Jun 7, 2014
I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.
So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.
View 2 Replies
View Related
Jul 20, 2006
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)
Here's what I need:
Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
View 4 Replies
View Related
Apr 16, 2007
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies
View Related
Jan 4, 2010
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies
View Related
Jan 19, 2009
I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.
Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.
But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.
While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?
View 9 Replies
View Related
Oct 18, 2009
is it possible to calculate the total duration of logged in from the data below?
TimeDateTimeExtCoDial Number08:02:00 AM11/10/200908:02AM203LOG IN08:03:00 AM11/10/200908:03AM2033448249908:05:00 AM11/10/200908:05AM207LOG IN08:05:00 AM11/10/200908:05AM132102445477708:08:00 AM11/10/200908:08AM207102445477708:11:00 AM11/10/200908:11AM1322908:11:00 AM11/10/200908:11AM1323008:12:00 AM11/10/200908:12AM2073008:12:00 AM11/10/200908:12AM2032908:13:00 AM11/10/200908:13AM132202631121108:14:00 AM11/10/200908:14AM203LOG OUT
Column 'A' has the time and extn# on column 'D' and indication of logged in or logged out on column 'F'
there can be several log in and log out by an extn.
View 9 Replies
View Related
Dec 17, 2013
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
View 2 Replies
View Related
Mar 13, 2009
I have a large spreadsheet with various data. On the first column I have a list of user ids, this is a unique id to each user. In each row there are data there are varies other data. I want to find a unique id and then add up the figures from week1, week2, week3 and week4.
So lets say I want to find user number 1 and the total for weeks 1, 2, 3 and 4, how would I do that using an excel formula? I have given an example below but I have simplified to spreadsheet.
IDWeek 1Week 2Week 3Week 4
139424236
255556044
334335434
461515350
529504694
618243830
View 2 Replies
View Related
Feb 28, 2012
Say I have a listing of cells
1,8,57,77,100, 104
I have a number I would like to find that is made up of a number of these cells. Lets say that number is 205, and as such the only possible values that could make that total are 1,100, and 104.
Is there a formula I can run that will allow me to find this number without manually figuring it out.
View 4 Replies
View Related
Nov 18, 2013
what im looking for is, i have two different rows i.e. col A and col C is the "type".
col B and col D contains "quantity".
i want in column E, listed all unique types in col A and col C and their respective total in front of the next column i.e. in col F.
col A.....col B.....col C.....Col D
150...... 6.........120..... 4
150...... 6........ 120..... 4
2.5....... 6
25 ........ 6........ 25 ....... 2
25 ........ 6 ........25 ....... 2
25....... 12....... 25....... 2
25 ........ 6 ........25....... 2
185 ....... 6 .......150...... 2
185 ....... 6....... 150 ...... 2
View 9 Replies
View Related
