I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same.
In order to ensure we are on the same page let's use the following associations:
Y = Quantity
X = Cost Per Item
Z = Order Total
So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
I would like to have something that looks like a running total, but isn't.
I have in a colomn 'rate' and after that a column for each month in a year. I would like to have at the bottom a total cost, so rate x hours. have a look at the picture.
is there a formula for this? ratemayjunejuli $10 2,02,01,0 $12 2,03,00,0 $10 3,02,05,0 $15 4,02,53,0
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell.
I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
I am trying to calculate players ranking based on their weight categories and attempts total, e.g. Category (50kg, 69kg etc..) Rank should be calculated automatically the highest total value of cat. 50kg is 1st second highest is 2nd etc. However, if tow players or more within the same Category have scored same point ranking will be calculated based on Body Weight less comes first and if both have similar weight calculation will be based on Start # first player comes first etc..
Table: Start # Name Body Weight Team Category Total Point Rank
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls
Here are some of the rules for the raw data structure:
1. First BOM level at very beginning is always "1"
2. All children of BOM level "1" have a BOM level of "2"
3. All children of BOM level "2" have a BOM level of "3"...
4. There are potentially many BOM levels (approx. 15 levels for full set of data)
5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc.
6. Data is always sequential so the children (and grandchildren) will always come after the parent
I want to allocate the Quantities which is available in DC based on Sales, Store OH and In Multiples of Pallet Rounding (PR).
For Eg. If I have 200 Qtys in DC, Excel should allocate these 200 Qty's in multiples of PR to highest selling stores and also consider the store On Hand.
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V.
Example:
In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.
I am preparing a daily report available in sheet1 with the actual input in sheet2 generated through a tool.
The report contain three columns i.e. total cases, Total correction done and Not corrected. From the total cases on a specific date, I need to calculate how many correction have been done and how many are pending.
E.g. 01 January 2013=5 cases are uploaded in the tool.
on 02 January 2013, it was observed that 3 cases were corrected and 2 are pending But 9 cases cases are uploaded on the same day in the tool .
So Total correction done (C3)=3 Not corrected(D3)= 11
I have a list that is numbered 1 2 3...ect. I want to have a cell that tells me the total number of entries. I have a I'm not sure how I could do this.
1 2 3 4 5 6
1 2 3
Total = ?
If I do a normal sum it would just give me all of those numbers added up. I want the total to read 9 (in this case) My list changes all the time so I want that total to tell me how many entries I have.
I am creating an employee database. Any simple method for recording then calculating a total of occurrences when someone is tardy. I thought of using an OptionButton but I don't know how it could be applied and stored in my database. This is the code for the form as it exists now:
I can achieve that with the use of SUMPRODUCT. However next month, I will then have to manually change the SUMPRODUCT formula so that it will extract data from the Feb column instead of the Jan column.
Is there a formula which will not require me to change the formula every mth? Ie. I can get my answer simply by changing the criteria?
I've got an excel spreadsheet that is using imported figures from another source. I'm just trying to create a macro that will locate the last cell in Column N, Then will total all the cells above it. I've got the below code to select the last cell in Column N.
myrow = Range("N1:N" & Range("N65536").End(xlUp).Row).Count For i = 2 To myrow Range("N" & i).Select
But I can't figure out how to do the sum part. I've tried to record a macro but to no avail. Since the number of rows will change each day, I need to somehow catch the range. This is part of a bigger project, but if I can get this part working, I think I'll be home free. Attached is a small example.
Basically, I'm trying to have a cell (eg.A1) where i can input a dollar value and when enter is pressed, on another cell (eg.A2) the dollar value gets added, then the original cell (A1) is cleared for the next input.
Then when I put the next input (A1) I want it to add on to this running dollar total (A2) and yet again A1 is cleared for the next input.
In addition to this I require an additional cell (eg.A3) that when each time a value is added in A1 it counts the times this is done.
I am working on an excelsheet containing a data dump from a manufacturing database. Inside this sheet i want to make subtotals of different production runs. The data that needs to be added up is in one big list between every production run there is an empty cel. So I want to make a formula that does: =SUM(one-cel-above-current:next-empty-cell-above)
In the file I have attached, I need to work out how to give the final total (cell G27) inclusive of VAT, dependent on the value assigned in cells K17-K26, which are explained in the list starting cell B29.
At the moment the value given is net of VAT, and I need the total figure in G27 to recognise that the first item needs to be at 15%, the second at 5% and the 3rd at 0%.
I cut alphabet letters out of MDF. I need to calculate the total weight of any letter. I have a program that will give me the area of any shape. In the picture below I have started with the letter “A”. B7 is the over all area. D7 is the small triangle. G7 is the total area minus the triangle D7. H7 is the thickness and I7 is the total volume.
The weight of my MDF I have set at is 850kg cubic meter. That’s set in A3.
I need the total weight of the letter in J7 to be in grams. The idea is, I put in the areas and set the thickness and it gives me the total weight.
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
The code below basically adds addresses to both the page and the oracle backend. However I am trying to work out how to limit the number of addresses they can add to 100. Therefore is it possible via code to calculate If Me.finish.Value minus Me.start.Value is greater than 100 then stop the code?
Based on above data if The Column heading is 1 then it needs to go to the below data and multiply by that value and at the end should give me the total for the person. note that the total time is captured in a different sheet.
Label 1 = Time when form initialized or opened Label 2 = Running Time (Live Time) Label 3 = Total Time (Label 2 - Label 1)
Now what I need is since Label 2 is live time which shows the running time, Label 3 displays the total time spent. I want to display total time as running time.
e.g. Label 1 = 13:01:00 Label 2 = 13:05:10 (running time/live time) which will keep ticking Label 3 = 00:04:10 (I want even this as running time which keep running when label 2 is running)
I'm trying to have a cell (eg.A1) where i can input a dollar value and when enter is pressed, on another cell (eg.A2) the dollar value gets added, then the original cell (A1) is cleared for the next input.
Then when I put the next input (A1) I want it to add on to this running dollar total (A2) and yet again A1 is cleared for the next input.
In addition to this I require an additional cell (eg.A3) that when each time a value is added in A1 it counts the times this is done.
Is there a method to calculate the total number of hours in excel. In my attached file, the excel treat the 24 hours as time and recalculate from 0 hour.
In the example above, E2 calculates the totals between 12/8 and 12/9 giving me 15:00. What I want the formula to exract is the total from Col D the total from 12/9 to the previous 24hrs.
12/8 has 2 entries, and 12/9 has 3 entries. The total I want to extract in 24 hours, NOT 1 day, is 14. What formula can I use that will calculate the totals in 24 hrs.
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
I have attached a sample workbook of what I am struggling with. It is for payroll, and I need to calculate taxes and pension as a percentage of Total W-2. The problem is that the 'Total W-2' column then deducts these taxes and pension.
Currently, the worksheet has about 30 columns out to the right using 'guesses' to back into these numbers, but I hope there is an easier way.
