Cost Vs Units Graph
I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):
Cost per unit for 0-10 units: $1
Cost per unit for 11-20 units: $3
Cost per unit for 21-30 units: $5
Cost per unit for 31-40 units: $8
Fixed cost: $100 (this is regardless of the number of units produced)
Basically I'm looking to construct a cost vs units graph.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Deleting The Units (letters) In Two Cell Ranges And Graph It
I would like to build a Macro to delete units (ex: 10V --->10) of two cell ranges that are not beside each other and eventually automate it to graph these two ranges because I will be running samples and have to produce a graphs for each sample. I tried the code below, but I can't figure out how to include 2 ranges without deleting letters in other cells in in between the ranges. for ex: I want to delete the non numerical characters in the two ranges C3:C1000 and H3:H1000 and graph these ranges.
View Replies!
View Related
Round Up In Units Of 80!
I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on. But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.
View Replies!
View Related
If Function- To Calculate The Units
I have a spreadsheet that keeps attendance. Now I need to calculate the units (1 unit= 15 minutes) the children are actual here. On a normal schedule they are here for 16 units but if the are late the units get subtracted. When entering the data, they would like to use letter instead of numbers.
View Replies!
View Related
Even Distribution Of Units Across All Prices
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.
View Replies!
View Related
How To Convert Units Within A Cell
What I am trying to do is take entered data in a spread sheet, and convert the units within that cells. I have several entries for data in this sheet. Here is what I want to do: -I have 104 entered in cell B2 -I want to convert that reading (which is ºF) to ºC in that same cell -I would like a button or selection from a drop down box to select Metric or Imperial I want to do this so if the whole spread sheet is entered in metric, I can select a name or click a button to have all the data switch to imperial. I know I can do this with two sheets in the same file, but I am not sure if this is possible to do in a cell I entered the data in. Any help would be great. I am using Office 2003.
View Replies!
View Related
Total Units For Each Customer
I have been tasked with tracking the expected income for our Advertising Department. (I work for a newspaper.) The rate I charge an ad depends upon the total number of advertising units a customer purchases during an entire billing period. Essentially: I need to have a total of column G for when column B matches the current row. I need this in column K. The value of K should be the same when the value of B is the same. My programing flow chart I sketched out checked to see if any row in column B that is above the current row matched the current row's data. If it did then it would use that row's value for the total number of units. If it did not it would add up the units from G when B matched the current column. I attached a document
View Replies!
View Related
Summing Certain Values In A Range Which Have Different Units
I have three columns. Lets assume i have One header row and 3 rows of actual data and the 4th row is for totaling column B values. Column A is a col of "Ingredients", column B is the amount of the ingredient (from col A) used. The value is either in Oz or Lbs. Can be either. Column C is a yes or no column. If the value is Yes then i want to have the value in col-B included in my overall sum which is in the 4th row. If the value is No then i do not want the value in col-b (in that row) included in the overall sum. Any ideas on how to do this? Thanks. david
View Replies!
View Related
Formula Return The Total Value Of Units
i need a formula that will return the total value of units which is greater than 20000 in col b between 5 and 30 days AgeUnits272,472,215.29134,354.83112,714,300.007-112,566.006-239,853.006-105,938.006-36,792.006-608,695.001827,632.007-21.00
View Replies!
View Related
Auto Populate The Number Of Units
i have been trying to write an Excel sheet in 2007 that can be given a random amount of (money) and show me the best way to spend it. Weapons: Name - Power - Cost 1 - 10 - 20 2 - 20 - 40 3 - 40 - 80 4 - 80 - 160 5 - 160 - 320 6 - 320 - 640 So i have 40K to spend, i want to eneter 40000 into a box and it will auto populate the number of units i can buy, always setting the weapon #6 as the priority.
View Replies!
View Related
Converting Units And Decimal Places.
I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered. In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4. Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?
View Replies!
View Related
Make A Graph That Will Graph Against Time
How can i make a graph that will graph against time? lets say i have a bank balance like this: 1/2/2000 $500 1/3/2000 $600 3/12/2000 $400 there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.
View Replies!
View Related
Sum Numeric Values That Include Units
I have lists of room areas in metres squared, I extract the web based lists to excel: for example. 10.56 m2 14.23 m2 21.34 m2 How do i calculate the total of these amounts? Is there a formular to remove the "m2"? I have tried using =SUBSTITUTE(A1,"m2","") which deletes the "m2" from the cell, but then I can't use =SUM(A1:A3) as this totals the 3 cells as 0. If i use =SUM(A1+A2+A3) the total is correct, but I have over 3000 cells in one column to add together. Is there a formula or something to remove all instances of "m2" or "m3" from the whole sheet or another way i can calculate the totals.
View Replies!
View Related
How Do I Set Up A Formula For Parts (or Units) Per Hour
I'm trying to set up a spreadsheet that tracks total hours worked and total units produced. Then I need to have a column that shows how many units per hour were produced. Currently, I have something like this: Column A is in elapsed time [h]:mm Column B is a Number with two decimal places Column C divides Column B by Column A However, I get strange results. For example: Column A is 6:24:00 Column B is 13 Column C shows 120.00 13 parts in 6:24 hours should be something like 2.1666 parts per hour!
View Replies!
View Related
Line Graph: (line Graph To Display An Amount Over Time)
I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value. A short example: Imagine you own 3 different stores and you're selling oranges. So your table looks like this: http://img179.imageshack.us/my.php?image=orangeshm4.jpg Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source): Date on the x axis, Oranges sold on the y axis, and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).
View Replies!
View Related
Calculating Cost Per Second
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network. Edit: Charges to network A is 1,79,- per minute after the first 20 minutes are spent. Charges to network B is 2,29,- per minute after the first 20 minutes are spent. To sum up: 1. The customer makes a call. 2. If there there are available free minutes, these should be spent first. 3. The customer is charged per minute, depending on network called.
View Replies!
View Related
Least Cost Routing
im working on a VoIP company, and having a hard time to figure out a result on a little time. the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns: I am including an image, sorry if the image seems to be heavy and large: as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers. While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate. Red marks=are not suitable or non-profitable buy rate for the sale rate. Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.
View Replies!
View Related
Calculating Cost
Problem - billing spreadsheet for prisoner fee. 1 - 8 hrs = $55 9 - 24 hrs = $55 + $65 or $120 Anything over 24 hrs - $65 for each additional (24 hrs) ($185) So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.
View Replies!
View Related
Transferring Cost From One Sheet To Another
I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?
View Replies!
View Related
Cost Based Mark Up
set up a cost based markup spreadsheet? I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value. I only know how to mark up values by a single percentage.
View Replies!
View Related
Calculate X Percentage Of Cost
I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.
View Replies!
View Related
Cost Average Effect
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 Replies!
View Related
Time And Cost Calculations ..
I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit. The source data I have is; 1 - Start time and end time of the job 2 - Any breaks taken during the job 3 - The number of staff it took to do it 4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates 5 - The number of punnets packed. With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39
View Replies!
View Related
Calculating Cost Based On Several Factors
i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago. I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information. For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month. Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month. Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.
View Replies!
View Related
Cost Price Vs Quantity Discount
I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +
View Replies!
View Related
Way To Check Whether Any Cost Centre Has Been Missed Out ...
I attached a workbook and it shows data in row 2 - 960. and in cells BA1574 -BM1604 are the summary by using 'sum if' function. As you can see, row 160 countercheck the results and it shows differences. So my questions are 1) any formula that i can use to ensure all cost centres are taken up in Col AZ1574 - AZ1604? I am sure that i have missed something out. 2) My goal is to summarise the total of each cost centre. Is there other options to reach this goal apart from using 'sum if' function? I would like to learn something new.
View Replies!
View Related
Manufacturing To Retail Cost Calculator
I am trying to make an EASY Manufacturing Cost to Retail pricing calculator. This calculator would have ability to include cost of goods, labor, markup etc of components manufactured and sold as retail products, example: small bookshelves versus large bookshelves or cabinets all have different materials (wood types, stain etc) in determining the final retail product costs that would reflect time of labor hours involved in producing to determine final retail costs. Should be simple in Excel 2000 (my version). I have the basic template created and have used Data Validation Drop Down Lists and utilized LOOKUP function. While my knowledge is limited in Excel, I am frustrated as how the LOOKUP function works, I can only get it to work where in the formula, the costs per unit are input manually, whereas I would prefer to have the data input automatically from columns of calculated wholesale cost plus markup per square foot data, ie: =lookup(A1, X1:X30, Y1:Y30) but get errors when doing this. Instead this works: =lookup(A1,{"pine", "oak", "birch"},{"3.99","7.87","5.15"}) and using this cell (A1) in my calculations for including the square foot costs in the final retail calculations. While this seems to work, it is not easily modifiable as costs change rapidly and would like to easily input the cost per square foot of the different woods in their own cells rather than in the formula calculation of the lookup. Hope that makes sense..... any suggestions? perhaps lookup is not the best function for what I am attempting? - sample file is attached, light green are notes of where my data is located.
View Replies!
View Related
ISDN Call Cost Formula
I have an Excel workbook with 2 worksheets, labelled as "codes" and "data" The "codes" worksheet contains three columns of data. Column A shows Country names Column B shows Country dialling codes Column C shows Cost per minute to the country ...
View Replies!
View Related
>>> Finding Lowest Cost/Vendor
I need to find the lowest price, mark it up and designate which vendor it’s from so the CSR knows which one to quote back to the customer. Besides going through it line by line, is there a way to do this? It seems like there could be a formula where it pulls the lowest of the 3 prices and vendor name into a new “price” and “vendor” columns and then I can go through and mark it up. Here’s a small sample of what the sheet looks like: Item #Desc V1 V1 CostV2V2 CostV3V3 Cost 123456brush Advance 2.56Weiler5.65Osborn3.25
View Replies!
View Related
Simple Job Cost Sheet
The job cost controller is not exactly what I need, so I have been trying to create my own. So far my progress has got me to be able to separate each laborer for each job. Here is my query: I have a drop down menu that lists ten different tasks(i.e. carpentry, painting, demo, etc.) and I want each of the ten tasks to separate themselves. Is this a vlookup function? How do I make excel listen?
View Replies!
View Related
Inventory: FIFO, LIFO And Average Cost
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 Replies!
View Related
Sum The Total And Find The Average Cost
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 Replies!
View Related
Calculating Manhours/labor Cost In 2003
I am having trouble trying to calculate cost for a specific task. I know this is something simple and I am going to kick myself when it gets solved, but I have total brain lock right now! Here is the example of what I am trying to do. A B C D E F # of people start finish time man hours labor cost 3 1:35 2:05 :30 1.5 $15.00 I am entering the values in A, B and C, with B & C formatted as TIME. D is calculated by =(C3-B3), but I am lost trying to calculate E and F.
View Replies!
View Related
Calculating Total From Number Of Minutes And Cost P/m
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 Replies!
View Related
If Then Or Else: Calculate The Cost Of A Cushion That Is Governed By It’s Thickness
use the “INDEX & MATCH” formula and I thank him very much for that. Now, I have another problem which I think the “IF & ELSE” statement may come into play. I am trying to calculate the cost of a cushion that is governed by it’s thickness. Example: ABCDEFGHI 1CushionB/FrontS/SideBorderQuantitySeat 2Feather121251£8.25 3Feather121241£7.32 4 B2 represents 12 inches C2 represents 12 inches D2 represents 5 inches 144 represents square foot The calculation is, F2=B2*C2/144*D2*£1.65*E2..............
View Replies!
View Related
Calculating Cost Dependant On Month And Category
I have a sheet with 3 columns. First one is a date in the format dd/mm/yy, second is category type (numerical 1-40) and then the final column is cost in the format 0.00. These columns will need to run from A2:A65536, B2:B65536 & C2:C65536 to cover all later additions. I need to work out a cost total for each of the categories in each month.
View Replies!
View Related
Determine Cost For Stepped Pricing In Seconds
The phone call is charged at 0.57 for the first 60 seconds, thereafter 0.0095 per second. Determine cost if duration (in seconds) is available? AND The phone caal is charged at 1.654 for the first 60 seconds, thereafter 0.827 for every 30 seconds.Determine cost if duration (in seconds) is available?
View Replies!
View Related
Minimize Cost By Calculating Best Binomial Distribution
I'm working on a problem that calculates data using a binomial distribution. The data derived from the binomial distribution is then used to calculate a cost. I would like to minimize cost by changing the number of " reservations". Can excel solver do this or is it too complicated? I have attached the file with what I'm working on. (Changing E1 to minimize E2 while Cells A9:A102 are calculating a binomial distribution)
View Replies!
View Related
Multiplying Cost By Variable Factors, Vlookup Not Working
I have an Excel 2003 situation I cannot resolve. In one workbook, I have a table of multiplication factors based on a numerical general ledger code. On another sheet, I have a data extract including that code and a cost. In the latter sheet, I need to multiply cost by the appropriate factor based on the GL code - IF Code=X, then cost*factor. Should be simple, but I have too many codes for a nested IF statement. I also tried a VLOOKUP, but since the codes are numerical, Excel interprets them to be in the thousands, while my factors are 1.xxxxxxx, and since the data being looked up thereby appears smaller than the keys, VLOOKUP will only return #N/A or ?NAME, depending on whether I try to nest it in an IF or not. I also have several thousand records per month to which this needs to be applied, so going manual is not an option. The calculating is done in column E, the original cost is in column AB, the key (GL code) is in column R, and the GL code range with factors is on the !Factors sheet (column A=Code, B=Description, C=factor). *I'm running Excel 2003 on WinXP Pro
View Replies!
View Related
Column Title Lookup: Find The Lowest Cost
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 Replies!
View Related
Figuring List Price - Cost To Show Discount Percentage
List Price $46.98 (e2) Net Cost $19.53 (e3) How do I enter a calculation that will show me my discount percentage from my supplier? (e4) I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item.
View Replies!
View Related
Formula (retrun The Right Cost For An Item, Depending On The Delivery State)
I have 3 different costs for an item, 15 different items. The costs very depending on the delivery state. Is it possible to write a formula that will retrun the right cost for an item, depending on the delivery state? if ap10 = "CA" then look up in range AH2 to AH18 if ap10 = "GA" then look up in range AI2 to AI18 if ap10 = "ME" then look up in range AI2 to AI18 if ap10 = "PR" then look up in range AI2 to AI18 if ap10 = any other state then look up in range AG2 to AG18 cell AP10 being the cell that the state is listed in Cell U10 being the cell that item is in (a drop down menu) This is the vlookup formula that will get one price. =IF(ISNA(VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2,FALSE)),"",VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2, FALSE))
View Replies!
View Related
First In First Out (FIFO) Accounting For Equity Trade's Book Cost
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.
View Replies!
View Related
|
TRACKER
