# 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.

## 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.

## 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

## 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.

## Calculate Two Different Prices Based On A Given Quantity

Aug 29, 2006

I want to be able to calculate a price based on a given quantity. But here is the trick.

Example: Widget comes in cases of 12 at \$1.00 per case. If the customer orders 36 widgets, then the cost is 36 x \$1.00.

If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at \$1.00 per case. If the customer orders 4 widgets then the price is 4 x \$1.15

But, if the customer orders 35. Then the first 24 are calculated at 24 x \$1.00 and the remaining 11 are calculated at 11 x \$1.15.

## Calculate Prices Based On Quantity Discounts

Dec 5, 2007

with outputting a table with calculations based on the quantity that the user enters, and depending on that quantity, will calculate the price based on if he/she will receive the discount.

I think I should use an IF statement and calculate depending on if they receive the discount or not, but I don't know how to do that for one type of product code and not have to go manually put it in one by one.

I've attached a speadsheet to show how I should output the data. The prices will be listed in column E, with the prices depending on the quantity that the user will input with an input box and then if he/she qualifies for a discount.

## 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 +

## 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

## Calculate Price Based On Minimum

Jan 1, 2008

Trying to create a Math formula that allows for Taking advantage of sale pricing.

In Excel the following:

Qty to Order (G3), Std Package(I3), Price per Unit(J3), Sale Price(K3), =

Extended Price(L3)

Example

2case X 40peices per case,1.00Reg Price,.90Sale Price = 72.00

Not quite sure how to handle the Sale price taking over regular pricing

## 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.

## 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?

## Formula To Calculate Price Based On Sheet2

Apr 23, 2009

I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged.

Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution.

## 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.

## Discount Prices Based On Order Quantity And Package Quantity

Nov 23, 2009

I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:

Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6

Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................

## 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

## Calculate The Quantity Of Each Size Of An Item

Nov 26, 2008

I need a formula that will calculate the quantity of each size of an item (XS,S,M,L,XL). For example to calculate the quantity for Mediums, I need the formula to look at column C, if M is entered, then look at the adjacent cell in column A for the quantity and formulate a total number (from rows 4-21) and enter the total quantity in C27. See attachment.

## Calculate Selling Price

Feb 7, 2008

I have a cost price in cell a1 I want to calculate the selling price.

If the cost price is less than 0.25 then the selling price is cost multiply x5 if the cost is between 0.26 & 1.00 the selling price is cost multiply x4 if the cost is between 1.01 and �4.99 the selling price is cost x3.5

## Calculate Average Price For Various Periods

Feb 14, 2014

Trying to calculate the lost revenue value for when a piece of machinary is offline for a period of time based on the hrly price during that particular period. The machines come on/off line at various times during the year so i am trying to make this a bit dynamic so i only need to add new info and not have to change the formula

Column F - Off date of machine
Column G - Restart date of machine
Column H - Hrly time
Column I - Hrly price

I thought this formula (an array) would work for me

=average(if((Column H>=Column F) * (Column H<= Column G), Column I)) but it only calculates that average price for the first period and not new average prices for other periods.

what adjustments I can make to my formula or is there a better formula to use?

## Calculate Average Price Per Ticker

Jul 19, 2012

I have the following data, where a negative number represents a sell of shares and a positive number a purchase and column C shows at what price the transaction happened.

A
B
C
D
E
F

1
Ticker
Quantity
Price

Ticker
Average Price

[Code] .......

And I've been asked to calculate the average price per ticker. For now it doesn't matter whether it's based on a first in first out principle or first in last out principle.

## Copy Item Number And Calculate Price As Replenished

Jun 9, 2007

attached is my spreadsheet. What I would like it to do is when someone marks a "N" or some other symbol in the box showing that supply not there; that excel copies the item #'s for the products listed and calculates up the prices for those items.
Thank you in advance for your help. If at all possible if it could calculate the order on another sheet such as sheet 3 that would be fantastic

## Calculate Proposed Selling Price By Entering Desired Profit Margin Percentage

Jun 23, 2010

Is there an excel formula that can calculate a proposed selling price by entering a desired profit margin percentage and knowing the cost of goods sold?

I know that gross profit is calcualted by subtracting the cost of goods sold from the selling price and that the gross profit margin is calculated by dividing gross profit by the selling price..but not coming up with a way to back out a selling price by just knowing the cost of goods sold and entering a desire gross profit margin?

## Forecast Quantity Based On Sales History?

Mar 29, 2013

I need to find a way of populating a column of forecasts based upon previous sales amount and price. For instance if I have apples on special for \$2 and previously sold 200 units on multiple occasions at this price but once off sold 1000 apples at special \$1, but normally they are \$3 selling on average 50. I would want to get a result of Forecast: 200, not 50 or anything else to far off

I've attached the sheet I currently use for work.

Dated tab: is my working sheet MerchTrend: Previous sales history, which is imported from POS system and unfortunately cells will change based upon sales

On the Dated Tab, price column includes multi buy prices (ie 2 for \$3) but the Merch Trend refers to these as individual sales (ie 2 sales for \$1.50) On the Merch trend, Price Type refers to promo style. (N for Normal Price, IA, S, R, IR, P are promotional)

promo sort example.xls

## Collect Total Quantity Based On Different Product?

Mar 15, 2014

I have a data where i need to collect a total quantity bases on different product. And these product will repeataly appear in a table several time. I have different datasheet every time i need to collect.

## Using Index & Match (rank Stores Based On A Quantity Of Run Outs They Have)

Sep 21, 2009

I am trying to rank stores based on a quantity of run outs they have. I have a list of stores in order with the run out quantities. I use the large function to figure out the rank but when you have two stores with the same quantity it only finds the first on in the list.

## Excel 2010 :: Pull Quantity Based On Date Range

Oct 27, 2011

Using excel 2010

In cell U24 I have this formula:

=SUMPRODUCT((\$E\$3:\$E\$2000=51014)*(\$F\$3:\$F\$2000=101)*(\$K\$3:\$K\$2000))

Currently, as you can see, this is summing up the criteria specified from row 3 to row 2000...(which i made the last row 2000 becuase I will never exceed row 2000 with info)

What I am trying to do is in cell T22 and U22 be able to plug in date ranges and make the above formula give me the sums of thos date ranges.

For example in T22 put in 8-1-11 and in U22 put in 10-1-11, and have the sum of that date range populate in cell U24.

Now ther kicker is this: some dates are repeated so I may have six rows of date 8-3-11....and they will have to be part of the sum total.....and ALSO the dates are not in chrological order....I may have six rows of 8-3-11.......then four rows of 8-10-11.....then maybe another three rows of 8-3-11, and so on and so on...this is for all dates. Sorting the dates in order is not an option.

## Return Rows Based On Latest Date AND Highest Quantity For Given ID

Dec 10, 2012

I'm trying to return a distinct list of rows that filter based on the latest date and largest quantity for each distinct AccountID and ProductID combination.

I tried some variant of the =max function, but I need two filters.

The purpose of this is to create a data set of all company accounts with the most recent number of products used to upload to a database.

The simplified and original data set is as follows:

AccountID
ProductID
Company
Date

[Code].....

## Excel 2003 :: Reposition Data And Sum Quantity Based On Matching Reference

Feb 15, 2013

I'm using excel 2003 and using VBA I want to convert data from my original data below.

The first row is just to identify the columns below it.

REFR1,REFERENC2,COMPANYNAME01,CURNTDATE,QUANTITY,ITEMNUMBER,PRICE
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 TB,65.9900
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 CH,86.5800
71721,121221142,EXAPLECOMPANY,2-13-2013,1.0000,MPG PB01,75.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,2.0000,MPLC001,146.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,1.0000,MPMC001,120.9900
71720,121238748,EXAPLECOMPANY,2-13-2013,1.0000,MPS007,63.9900

To be converted to the following:

"COMPANYNAME01",
"TOTALITEMS","REFR1","REFERENC2","CURNTDATE","0","0.00","0.00"
"ITEMNUMBER","QUANTITY","PRICE","TOTALPRICE",

"EXAPLECOMPANY",
"2","71723","121222010","2-13-2013","0",,"0.00","0.00"
"MPG TBS01 TB","1.0000","65.9900","65.99",
"MPG TBS01 CH","1.0000","86.5800","86.58",

[Code] ..........

The converted output data above can be within the same document or output to a text file.

## Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is \$1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be \$1494.40.

Windows 7 Ultimate / Excel 2010

## Adjusting Price Based On Volume?

Sep 17, 2010

I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.

I couldn't find a lot of info but I also may not know where to look.

example:

if (b2<62) then price is (\$sheet2.b2)
if (b2<124) then price is (\$sheet2.c2)
if (b2<372) then price is (\$sheet2.d2)

Is this even possible to put into a formula?

## Calculating Price Based On Profit

Jun 26, 2012

I'm using a spread sheet to determine what I should charge customers. I want to make at least \$300 profit on every job. If my profit is not at least \$300 then I must up my price until profit reaches \$300. Currently I'm manually adding a percentage (Factor) to the original price until I reach \$300 profit. In the image below:

A is the original price
B is the factor by which I multiply the original price to increase it
C is the new figure which is reached after multiplying (1+B2)*A2
D is the costs related to that job (I enter these manually on a separate sheet within the workbook)
E if the profit (C-D)

I use a factor in B because when I increase the price, then I have to increase several parts of the job by the same percentage so I multiply each part by this factor to increase the price of each by the same percentage.

Now what I would like to do is if E2.