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...........................
View 5 Replies
ADVERTISEMENT
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.
View 14 Replies
View Related
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.
View 5 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
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 2, 2008
I have inserted the sheet. As you can see sheet1 has some Products codes in A, Products in B, Layer Quantity in C, In G and H I have Sales and Stock and in I is the order quantity. I would like for I the order quantity to automatically do the order when the sales and stock are shown in G + H. I need this to be based on the Layer Quantity in C.
So ( I5 ) would be 18 because I have 20 left and sold 17, the layer is 18 and 18 would cover the sales. I 14 would be 90 because 90 + the stock of 35 is 125 double the sales going up in 15s whick is the layer quantity. I need the Order quantity and the stock left to be at least double the sales but go in layer quantity
View 4 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
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
View 1 Replies
View Related
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.
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
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.
View 2 Replies
View Related
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.
View 2 Replies
View Related
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].....
View 7 Replies
View Related
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.
View 1 Replies
View Related
Mar 30, 2014
I have a table like this:
QTY PRODUCT
---------------------
1 AB111
3 CC332
2 DA221
Is there a way to display that information on another sheet so that each individual product is on its own line with a QTY of one? I can do it with VBA but was wondering if there is an alternative?
QTY PRODUCT
---------------------
1 AB111
1 CC332
1 CC332
1 CC332
1 DA221
1 DA221
View 8 Replies
View Related
Oct 6, 2009
I have 3 columns:
in first cell (A1) i have a 553482300040
in second cell (B1) quantity: 1000
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 500.
View 9 Replies
View Related
Jun 10, 2014
I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?
My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?
Quantity Tracking.xlsx
View 5 Replies
View Related
May 13, 2014
I have data in ms excel and in column "F" is the Qty
If the Qty is Less then 40 then extract the data and put into the sheet "Less then 40"
I have attached sample excel file : Extr.xls‎
View 2 Replies
View Related
Jun 18, 2009
I have a INDENTED list from a cad software that looks like this.
It gives me the quantaty of each position. For instant in the list
I have 5 of 1.1 but because 1.1 is part of 1 makes my total 3x5=15
I have 2 of 1.1.1 but because 1.1.1 is part of 1.1 which again is part of 1 makes the total 2x5x3=30
POS QTY Total QTY
1 3 3 (=3)
1.1 5 15 (=3x5)
1.1.1 2 30 (=2x5x3)
1.2 2 6 (=2x3)
1.3 1 3 (=3x1)
2 1
My question how to write a formula that automatically calculates the total quantity.
View 9 Replies
View Related
Mar 9, 2007
I want to have the print quantity to be the following:
textbox1 divided by =VLOOKUP(textbox2,Kits!$B$3:$F$25030,3,FALSE)
I need to really do the following:
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:= "formula = textbox2/VLOOKUP(textbox2,Kits!$B$3:$F$25030,3,FALSE)", Collate _
:=True
View 9 Replies
View Related
Apr 24, 2008
I'm guessing it will be some sort of nested loop but I'm just going round in circles trying to solve it. I need to calculate a cumulative value based on the following criteria:
1. Pre Qty - can be any integer value
2. Qty - can also be any integer value
Accumulate values by looking up the Pre Qty + 1 then add all quantities * values based on those provided in a table giving different to - from ranges. Attached is a spreadsheet
View 4 Replies
View Related
Jun 13, 2014
Find attached formula on b2 , assume some numbers on a1
stk quantity remarks.xls‎
View 6 Replies
View Related
Jul 12, 2014
I have 44 cells each in columns D & E, column D being a class of item, and E being the quantity of that class, and I want cell G42 to show the total quantity of the class. I have used =COUNTIF(D5:D49,"A") and am only showing the number of A's (A being the class) in column D. What formula can I nest in this equation to get my desired results?
View 4 Replies
View Related
Jan 8, 2014
Basically i need a formula that will look at the message content on a seperate sheet (Data sheet on the attached example) and multiple by the volume of the message type to return a value on a seperate sheet.
The problem i have is our messages appear in the data as seperate lines based on the entire message content, with a quantity of how many times that exact message was sent on a day DESPITE there being many variations of a message, they may all only equate to 1 or 2 actual message types.
In the example you will see the Data tab showing Example message 1, Example message 1.1 etc. All of the Example message 1-1.5 are actually the same message type (same applies to Example message 2) and i need to be able to have formula that groups and counts these to return a value by message type * by the quantity per day. So in the example, Example message 1 would return a result of 10 on the main sheet.
View 5 Replies
View Related
Sep 29, 2008
I have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.
In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.
View 3 Replies
View Related
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.
View 2 Replies
View Related
Apr 24, 2014
So i have working printing macro code in excel that sends files to selected printer based on last 3 characters, when i click a button only 1 file per click is send to printer but i want to send more files (quantity is listed in row D). I think i need to add quantity row to shell command for printing so it will send not only name of pdf file but quaintity too:
[Code]....
Here is a rough look in excel and rows : PDF.jpg
And here is whole code:
[Code] ......
View 2 Replies
View Related
Apr 22, 2009
I am trying to enter a formula that will automatically suggest a purchase quantity by comparing the pieces needed against the required package quantity. I have attached a small example workbook, which has a description of the requirements for the formula, but I will also incude that description in this post.
Problem Description:
Our purchasing guideline is that we MUST order product in package quantities, as the vendor won't ship a partial package. The required package quantity for each part is shown in column B. If the pcs needed qty in column A is less than half the buy pack qty, you buy zero. If it is half or above, you buy a full package.
I want to put a formula in column C that will recommend the suggested buy quantity. The formula would need to compare the pcs needed to the buy pack qty, and put the suggested buy qty in column C. It should also consider multiples of the buy pack qty, such as if the buy pack is 5, and the pcs needed is 12, the formula would suggest 10 pcs. If the buy pack is 5, and pcs needed is 13, the formula would suggest 15 pieces, etc. I have manually entered the sugg buy qty for the first 22 rows, to give examples of what value the formula should return.
View 2 Replies
View Related
Jul 13, 2013
I get a list of data through xml data import
column A is dates WITH time, appears like so: 2013-07-13 20:25:02
column B is the product name
column C is the quantity
now I want to search certain items sold between two dates AND times which will then give me the total amount of that item sold between those dates and times
now I know how to do this with date but have a problem doing it with dates and times
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
