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

Sum Up The Totals Of 'quantity X Unit Price' For Each Product

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

Lookup Price Per Unit Based On Quantity Sold

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

Get And Average Price For Total Quantity Of Jacks?

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

Calculate Price Based On Quantity

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

Delete Some Rows Subtotal By Age Group Then Grand Total

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

Identify The Unit Price At The Highest Qty?

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

Macro Allow To Total The Data On The Total Sheet Depending On What Unit Number Is Selected

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

Cost Price Vs Quantity Discount

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

Quantity*price Returns An Error

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

Sum Quantity And Price Across Sheet Based On Items

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

Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day

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

Grand Total Of All Sheets

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

Excel 2010 :: How To Reference Unit Price In List Two Based On Date In List One

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

Grand Total Column In Chart

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

Table Grand Total To Count

Jun 24, 2008

title says it all - I'm sure this is possible but can't figure it out

View 4 Replies View Related

Merged Cells: Format The Cell So That If There Isn't Two Lines Of Type In The Description It Remains Aligned With The Quantity And Price

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

Pivot Table Grand Total Average

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

Select Grand Total Form Combo Box

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

Macro To Click In The Grand Total Of Pivot

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

Plotting Grand Total On Pivot Chart?

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

Show ONLY Grand Total In Pivot Table

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

Format Column Containing Grand Total To Set Width?

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

Return 'Grand Total' From Subtotaled Data

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

Pivot Table Grand Total SubCategory

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

Including Grand Total In Pivot Chart

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

Pivot Table Grand Total And Sum/count

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

Conditional Formula - Calculate Only If Net Unit Is Zero

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

Hide Grand Total For Just One Column In Pivot Table

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

Excel 2010 :: Replacing Grand Total With Difference

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







Copyrights 2005-15 www.BigResource.com, All rights reserved