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.
View 1 Replies
ADVERTISEMENT
Nov 23, 2009
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.
View 9 Replies
View Related
Nov 22, 2007
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.
View 7 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
Apr 21, 2009
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
0 - 99,999 0.05
100,000 - 499,999 0.04
500,000 - 999,999 0.03
1,000,000+ 0.03
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.
View 5 Replies
View Related
Oct 21, 2011
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
View 2 Replies
View Related
Dec 27, 2012
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.
View 2 Replies
View Related
Apr 22, 2009
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?
View 9 Replies
View Related
Apr 7, 2008
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 9 Replies
View Related
Jul 15, 2008
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
View 9 Replies
View Related
Jun 18, 2013
I have an issue when I use the sumproduct formula, it only sum the quantity and price orderly which is not matched
I have attache the file for example : Sale monthly.xlsx
View 9 Replies
View Related
Jun 9, 2008
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.
View 2 Replies
View Related
Feb 10, 2007
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.
View 9 Replies
View Related
Mar 8, 2012
I'm using Excel 2010 on a Windows 7 machine.
I have one sheet with a large list of dates and total prices.
I have a second sheet with a list of dates and unit prices.
I want to divide the total price in my first list by the unit price on my second list.
I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.
My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.
View 2 Replies
View Related
Aug 29, 2007
Sometime the ansewer in so simple I look past it. But how do you show the automated grand total in a line chart.
View 9 Replies
View Related
Jun 24, 2008
title says it all - I'm sure this is possible but can't figure it out
View 4 Replies
View Related
Apr 22, 2006
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
View 2 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
Mar 23, 2009
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.
View 2 Replies
View Related
Feb 11, 2010
Iam just looking for an macro which can do a double click in the grand total column of a pivot table.
View 2 Replies
View Related
Sep 13, 2010
Is there a way to plot the grand total from a pivot table on the pivot chart?
View 9 Replies
View Related
Feb 6, 2013
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.
Column Labels
Pipeline
Best Case
Commit
Closed
[Code]...
View 4 Replies
View Related
Aug 30, 2013
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.
View 9 Replies
View Related
Jul 13, 2007
Is there some code that will return the 'Grand Total' figure from a set of data that has had 'Subtotals' applied to it?
View 9 Replies
View Related
Jun 12, 2008
I have a Pivot Table With 2 Row field:-
1st Column = Product - Apple and Orange
2nd column = Region - US and Europe
Now Pivot Table look like this
Apple - US: 100
Apple - Europe: 200
Orange - US 50
Orange - Europe 20
Total - 370
How to show instead of Total - 370
Show:-
Total - US 150
Total - Europe 220
Master Total - 370
?
View 9 Replies
View Related
Nov 6, 2008
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?
View 9 Replies
View Related
May 15, 2007
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?
View 4 Replies
View Related
Jun 27, 2006
Here's the table for one game
Date-----Buy(Unit)-----Sell(Unit)-----Net Unit-----Price($)-----Net
Profit/Loss($)
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)
Number in bracket means negative.
## means empty cell.
How can I type a formula, so that every time there's no stock left (ie zero net
unit), it will calculate the net profit once.
View 83 Replies
View Related
May 19, 2013
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??
View 2 Replies
View Related
Apr 15, 2014
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!
PROBLEM.jpg
View 4 Replies
View Related
