Discount Prices Based On Order Quantity And Package Quantity
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Two Different Prices Based On A Given Quantity
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 Replies!
View Related
Calculate Prices Based On Quantity Discounts
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 Replies!
View Related
Cost Price Vs Quantity Discount
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 Replies!
View Related
Calculate Price Based On Quantity
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 Replies!
View Related
SumIf: "I" The Order Quantity To Automatically
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 Replies!
View Related
Lookup Price Per Unit Based On Quantity Sold
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 Replies!
View Related
Total Quantity Calculation
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 Replies!
View Related
Print Quantity As Formula
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 Replies!
View Related
Sum Quantity Of First Column
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 Replies!
View Related
Cumulative Value From Quantity Lookup
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 Replies!
View Related
Summarise A Varying Quantity Of Worksheets?
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 Replies!
View Related
Counting Quantity Of Numbers, With Conditions
in a2:a277 i have numbers 1-10, randomly. in m2:m277 i have numbers 0-4, also randomly. i want to count how many of the 1s in column a have a corresponding 0, how many have a 1, how many have a 2, etc. if you just give me one formula i can manipulate it for all 40 outputs i need,
View Replies!
View Related
Calculate The Quantity Of Each Size Of An Item
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 Replies!
View Related
Quantity*price Returns An Error
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 Replies!
View Related
Formula To Suggest Purchase Quantity
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 Replies!
View Related
Automatically Insert Rows With Identified Quantity
How do I automatically insert rows given the identified quantity to insert and copy the contents of the previous item on the insert rows. Does that make any sense a1 - abc a2 - def a3 - ghi I need to insert 10rows below a1, and copy a1 across the inserted rows. Now I have a ton of this case currently do it manuaaly, it might take me a lifetime to complete.
View Replies!
View Related
IF Calculation: Calculating Costs Of Products Ordered Which Imports Quantity And Descriptions From Another Sheet
I have a form for calculating costs of products ordered which imports quantity and descriptions from another sheet. The imported info and basic calculation of appropriate cost based on ordered quantity seems to work okay. These formulas are as follows: First column: =IF(ORDER!A5>0, ORDER!A5, "") inserts the quantity ordered Second Column: =IF(ORDER!A5>0, "Part Description and Part Number", "") inserts the product description Both those formulas return a blank cell if nothing is ordered. Then using an outlying cell (G3) I enter the following formula to determine appropriate cost based on total order quantity e.g. $2.57ea if total ordered quantity is less than 50 and $2.37 if total quantity is greater than 50.. {Q: How do I put a third pricing level in here??} =IF(SUM(A5:A27)>50,"$2.57","$2.37")................
View Replies!
View Related
Sum Up The Totals Of 'quantity X Unit Price' For Each Product
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 Replies!
View Related
Conditional Statements Using IF: Get A Discount (no Discount) Not To Apply The 10%
I have attached a 2004 excel document that has a conditional statement that says, if an amount is over $400.00 you get a 10% discount, else, the cell says no discount. That is working fine. I also have a absolute cell ready to do the math for the 10% amount. There is also a column that shows the discount amount and column that shows the amount after the discount has been applied. Since I have been just doing one calculation and auto-filling, my problem is, how do I get the record that doesn't get a discount (no discount) not to apply the 10%?
View Replies!
View Related
Discount Based On Amount?
I need to create a price list a we give a discount based on the price of the product for example:.... All products over £100 have a 10% Discount, how do this and also at the sametime round the value up to 0.99p. ie Product ZXY123 is £123.99 10% off is £111.59 we need the calculated price to be £111.99.
View Replies!
View Related
Calculating A Discount Based On Certain Rules
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100. We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on I use Excel ver 2003. Trust my query is reasonably clear?
View Replies!
View Related
Re-Order Columns Based On Column Order Of Another Worksheet
I need a way to re-order an excel worksheets columns based on another worksheet. Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required. How can i reorder the columns without physically having to cut and paste the columns to match? I have attached a sample spreadsheet. You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
View Replies!
View Related
Names In Alphabetical Order Based On Totals
Spreadsheet consists of the following: Worksheet 1 B10 : B49 = Names AO10 : AO49 = Amount Worksheet 2 I want it list the Names only from highest to the lowest from worksheet 1. If two Names have the same total then I want it in Alphabetical order towards who will be listed first. Example: Worksheet 1 B10 Sam AO10 10 B11 John AO11 14 B12 george AO12 16 B13 Steve AO13 9 B14 Carlos AO14 9 Worksheet 2 george John Sam Carlos Steve
View Replies!
View Related
Discount A Row
Is there a way to insert a discounted row at the top of an existing spreadsheet? I'd like to name it "0" or "Row Zero" or "MasterRow" or something similar, such that the existing rows retain their original numbering, and none of the existing code is effected.
View Replies!
View Related
Discount Function In Macro
I have a quote system which takes data from tabbed sheets if there value is greater than 0, then does a simply formula which is then times by a discount which found by searching for the sheet name and then attaching the appropirate discount. In short this means that the whole forumale doesn't work and is not displayed
View Replies!
View Related
Macro To Set The Order In Which Worksheets Should Be Printed (order Is Changing Daily)
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem: I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it? Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on... Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store). Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
View Replies!
View Related
Track The Levels: Club Is 0 To 19 Points And Yields An 11% Discount
I have a 4-level program. The levels are Club, Bronze, Silver and Gold. Each is awarded when a certain amount of points are gathered. Club is 0 to 19 points and yields an 11% discount. Bronze is 20 to 44 points and yields an 12% discount. Silver is 45 to 59 points and yields an 11% discount. Gold is 60 and better and yields an 11% discount. I am looking for a way to total the points and have the spreadsheet automatically calculate the leve and percentage. So if my total points is 22, I would need the formula to return "Bronze" in one cell, then "11%" in the next.
View Replies!
View Related
Figuring List Price - Cost To Show Discount Percentage
List Price $46.98 (e2) Net Cost $19.53 (e3) How do I enter a calculation that will show me my discount percentage from my supplier? (e4) I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item.
View Replies!
View Related
Rounding The Prices
I am looking at trying to do a complex rounding within Excel. I have a spreadsheet of all my product with my cost. I then convert to show what margin I want to sell my items at and then round to .99. What I am looking at doing is getting my pricing more uniform so that instead of having 23.99, it is 24.99 or 20.99 it is 19.99. I want all the numbers in the ones' spot to round depending on a set parameter. I have played around with if statements, but can not get it to work fully. Basically if the ONES number is 0 to 2 I want it to round down to 9.99 (ie first was 21.99 I want it to be 19.99) from 2.01 to 4.98 to round to 4.99 and then 5.00 to 9.98 round up to 9.99.
View Replies!
View Related
Calculate Amount Of Days Paid In Advance And Apply Percent Discount
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message. give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount? attached is the start of the assesment question. You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid. Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received: If students pay the course fee less than 7 days prior to the course commencing then they receive no discount. If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%. If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%. If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
View Replies!
View Related
Total Weekly Prices
I have a spreadsheet with rows of entries that are listed by date. I would like to create anther worksheet where i can refer back to the other sheet and calculate the total prices per work week. Is there a way to do this using a function(s)?
View Replies!
View Related
Update Ticker Prices
I have to update our ticker(US and Can stock) prices manually in our portfolio system. I use www.bigcharts.com as our source . I can import the file in Excel. So what I would like to do is update the prices in Excel from the bigcharts.com. Not just today's prices but prices from previous day or two as well. Once I go to the website, I click historical quotes, then type in the stock symbol, and the date ,then write down the price. Would Excel web query be the best way to approach this? If so, how would I import the the price for that particualr ticker for that date from the website into my excel?
View Replies!
View Related
Even Distribution Of Units Across All Prices
Refer to the attached sample workbook, (Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.) Cell F19 should contain =D19*B19 Column 1- These values will not change and represent price/cost of each UNIT Column 2- "UNITS" the quantity or number of items at column1 value Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2 Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed Ideally speaking, As few units in one row as possible to allow for a good mix. For example: What i don't want is $9000*86 for 774k. If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000. I believe a Most Even distribution of units will work fine. After the distribution/population of fields in column2; D29 must equal F3 F29 must equal F7 I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed. The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.
View Replies!
View Related
A Formula For Copying Prices With Exceptions
I am having a problem combining two databases. I have database E and F. I need to take any price that is $0.00 from Database E and replace it with the data from database F. The catch is that there isnt always a price to replace it with. Also, there are parts in database E that do not appear at all in database F. Below is an example (there are about 20,000 lines of data total) In the examples above I have placed both databases together. Column four has the database designation. As you can see the 1748 Hose reducer has a price for F but none for E. Basically I need some formula like the following: If (part number xxx) and (part number XXX) from column 1 are the same, replace the price data from E with the price data from F but only if E = $0.00 Is this possible?
View Replies!
View Related
Comparing Items And Calculating Prices
I have attached a sample spreadsheet for reference. What I am trying to do is eliminate column 'J' on the Distribution tab. I want to use column 'C' on the Totals tab to calculate the subtotal based on matching the products on the Distribution tab in column 'E' with the Products list on the Totals tab in column 'A'. Once the match is made, I need to multiply the associated price in column 'C' on the Totals tab and the associated quantity in column 'F' on the Distribution tab with the result going in column 'K' on the Distribution tab. I tried =VLOOKUP(E:E,Totals!$A:$A,3,TRUE)*F:F and =SUMPRODUCT(SUMIF(E3,Totals!A3,F3)*Totals!C3) but neither works and I do not know what else to try.
View Replies!
View Related
Linking Codes To Prices In Another Workbook
I have an excel sheet that has a list of codes and next to each code there is a price list for that particular item. What I am trying to do it: I have another workbook that I use to type up quotes. In this workbook there are 2 columns: one with the code for the item and then another with the price thereof. What I'd like to do is when I enter the code of the part, I'd like the price to be referenced from the other workbook and be entered into the price column.
View Replies!
View Related
Prices Not Rounded To 2 Decimal Points
I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty. I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points. Also dont know how to integrate it into existing formulas and macros
View Replies!
View Related
Adding Prices On Top Of VLOOKUP Formula
I have a sheet with a price list of papers and sizes. I then use this list to calculate the price of each order. It's a basic VLOOKUP and it returns the price. (this is the section outlined in blue on the demo). However, i now want to add on another section so that if the order has Lamination (Gloss or Matt) this automatically adds on 20p or 25p respectively onto the sheet price. (table outlined in red but doesn't yet work).
View Replies!
View Related
Grouping Same Part Numbers And Adding Their Prices
I have the following Macro which groups same part numbers in an excel sheet and also creating a blank row after each (or set of same) part numbers. Their prices are in the adjacent cells. I want to add the price cells and the total to appear in the blank cell below the prices. I have one part number in Cell say A1, Its price in Cell B1 i have another same part number in A2, its price in Cell B2 (The macro has grouped them together) Now I want to add cell B1 & B2 and the result in B3 (Row 3 is blank, created by a macro after each group of similar part numbers) This process is to be repeted in the entire worksheet. Sub InsertRow_At_Change() 'part number Dim LastRow As Long Dim X As Long LastRow = Cells(Rows.Count, 1).End(xlUp).Row Application.ScreenUpdating = False For X = LastRow To 3 Step -1 If Cells(X, 1).Value Cells(X - 1, 1).Value Then If Cells(X, 1).Value "" Then If Cells(X - 1, 1).Value "" Then Cells(X, 1).EntireRow.Insert Shift:=xlDown End If End If End If Next X Application.ScreenUpdating = True End Sub
View Replies!
View Related
Sort Out All Bid Prices For A Specific Security
I have a security order list. This consist of several columns most important are Column A Column B Sec a 15,00 Sec b 14,50 Sec c 16,00 Sec a 15,10 Sec a 15,70 Sec b 16,70 Sec a 14,80 The first column gives the name of the security, the second column gives the bid price. What I want is that I want to sort out all bid prices for a specific security. Vlookup works fine but again it just gives the first record. How can I get all bid prices for security a on a new worksheet without blank cells. If possible I would like to have it sorted as well.
View Replies!
View Related
Formula- Sheet For Calculating Prices For My Work
I wanted to do a sheet for calculating prices for my work. I want to have 2 cells where in one I would type in manufacturer price and in other one I would get my price after markup. I have a list of price ranges that I don't know how to make a formula for. For example: If price is $0-$74.99 than add $50 If price is $75-124.99 than add $75 If price is $125-174.99 than add $100 and so on til $7000
View Replies!
View Related
Altering A Existing Formula For Converting Bond Prices
-------------------------------------------------------------------------------- Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula: =LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32) This allows me to type 101-16 in and excel recognizes this as 101.5 My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4). I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?
View Replies!
View Related
|
TRACKER
