Calculate Grand Total From Multiple Rows Of Quantity And Price / Unit
Jul 18, 2014
For this ordering tool, I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the ordering sheet (we still fax orders...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.
I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.
I looked and messed around with For loops, but I can't make it work.
i am currently working on cost analysis of large projects.
I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product.
I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.
I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.
Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.
I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns
On a separate worksheet I want to add I6+I7, depending on the value multiply it by the value in the second column. So if the total is 520,000 then multiply it by 0.03.
Import Financial System -Recaps Trial BalanceFrom Date:31-Jul-2011,To Date:27-Aug-2011, Requested By:Jenny Drumm,30-Aug-2011 9:28 AMAge Range: 0 - 30, No. of recaps: 231 Broker File NumberAgeVendor/Co/DeptFirstBankAssistsFRTBRODTYLoadsInvInvClaimsGL AcctTRUETRUEBalanceCostFeeRecptsAdjRecptsROE VarLC Var
[Code] ........
There are more age groups, & some groups have hundreds of lines. Each of the total line in the report has a number plugged in by the system, which, because of rounding, may be off by several cents. I figured out how to move the rows with Broker File Numer, etc & the row after it up above the first Age Range.
What I need to do next is:Delete all rows between "Age Range: ....." and the start of that range's data.Delete all blank rows between "Totals" and the next "Age Range: ...."Delete 5 rows after "Grand Total"Put a formula in the row immediately above each age range's "Totals" for each column, summing all the data for that column, in that age range. (Hopefully with the word Total in column B of that row, too)Put a formula in the row immediately above the "Totals" after "Grand Total" that adds together each of the Age Range totals.(I did figure out how to get the "Balance" cell in each row to sum that row going right to left, so at least I got a start! For that I'm using
Code:
LR = Range("C" & Rows.count).End(xlUp).Row With Range("R10:R" & LR) .NumberFormat = "#,##0.00_);[Red](#,##0.00)" .HorizontalAlignment = xlRight .VerticalAlignment = xlTop .FormulaR1C1 = "=IF(ISNUMBER(RC3), Sum(RC5:RC17), " & Chr(34) & Chr(34) & ")" End With
I have a list of part numbers that have duplicates because each time there is a different unit price the part number is listed.In my excel file, I also have quantities for each unit price the various different part numbers were sold at. I need a way to
identify: for each part number, what the unit price was for the highest qty sold. I am involved in a project at work and this information is needed.
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following: Within a workbook the 1st sheet is the data entry. In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example: Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry. G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
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 +
I am using MS Query to join 3 worksheets together.
I need to calculate one field but it keeps returning an error. the 2 fields are part of the same table calle "Trades"
The fields are : Quantity and USD Price
USD Price is calculated initially in the worksheet using the current exchange rate. I can display each fields separately (no problem) but when I try to multply the 2: I get a syntax error.
Things i have tried: I tried to multiply Quantity by 2 and it works. when I try to multiply USD Price by 2 I get the syntax error.
I also tried to copy the values (not the formula) into a new column called USD Price2 and: - Quantity*USD Price2 : syntax error - USD Price2*2: Syntax error
I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.
I have several files that are alike except that some have a different number of worksheets ion them.Some may have 12, some have 30. I need a macro or code that I can put in each file on the last sheet that will calculate the totals of L25 of each sheet (All Sheets) and put the Grand Total in the last sheet on cell M25.
Again,Some of the files have more sheets than the other , but I'd like the code to total L25 of all sheets, regardless of how many there are and put the Grand Total on the last sheet in cell M25.
I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price
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.
I have 2 combo box: the first one is called combo box1 and the 2nd one is called combo box2. I simply want whenever a name is selected from combo box1; i want a Grand Total to be selected automatically from Drop Down box2. I have several names in the Combo Box2 and Grand Total is always the last. So note that the list of names in Combo Box2 is dynamic and they keep changing all the time. I have attached a sample for more details.
I am building a Pivot table and I need to show ONLY the Grand totals at the end of the table. In the example below I want to show only Total volume Stage movement at the end and not the subtotals in the columns.
Can I use a macro to format a column that contains the words 'Grand Total'?
When I paste over the pivot it has reduced the column width and since it is all being produced by a macro I dont want to be lazy and do manual formatting.
I am still pretty new with pivot tables and charts. Is there a way I can include the Grand Total number within my pivot chart without altering my source data?
I started a pivot table for our budget and on the left side I have the account names and about 4 columns of applicable account codes to which I turn on and off when needed. On the right side I have all the budget numbers divided by quarter and halfs. On the right side, I can drop any list of numbers and it does the sum but for some reason, when I drop my 4th quarter numbers, it gives me a count and not sum. How do I change it to sum?
Second problem: I have grand totals number going down the columns meaning I have a sum of all my 1st Quarter numbers but I do not have them going across each line on row. How do I add that?
Third problem: I forgot a list of account codes to be put on the left, how can I add it?
I have a pivot table the has a listing of potential customers and I have included meeting dates in this table. The problem that I have is that the Grand total adds up all the dates and I have a total that doesn't make sense.
I want to hide the total for the 'Date Meeting Setup' column and keep it for the others. Can this be done??
As you can see in attached file (hopefuly you can see it) pivot table Summed sales in 2014 and 2013 in column GRAND TOTAL. Insted of summing this two years, I would like to show difference between them, but keep this layout!
