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.
View 14 Replies
ADVERTISEMENT
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
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
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
Nov 11, 2008
I am filling out an order form. My values are being entered as sizes. I would like to caculate any entery as 1 X the amount of the item, but the entry is for example.. "YS" and not the number 1.
View 4 Replies
View Related
Jul 9, 2012
I wrote code that works just fine, however, I realized there were parts of the data I was using that had 0's in it for holiday.
Bottom line, this is code that calculated the weighted average of prices for 12 months (12 rows across) and Drows across. I'm trying to use the "do while is empty" approach but can't get the syntax to work. Here's the code
Code:
For r = 1 To drows
Totwavg = 0
Tothours = 0
priceavg = 0
For n = 1 To ncontracts
[Code] .....
View 1 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
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
View Related
Jul 16, 2014
I have a price list from my supplier with the new prices.
I then have my Accounting software where I need to update the cost and retail prices. The problem I have is the Accounting software has allocated it`s own Unique ID for each item. So in order for me to bulk import this I need to keep this unique ID with the Actual Product ID together otherwise it will duplicate the product.
Here is a example
Sheet to be updated (Cost and Retail only) from PRICE LIST SHEET
Uneque ID
SKU
[Code].....
View 1 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
Dec 15, 2008
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 9 Replies
View Related
Sep 13, 2009
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 14 Replies
View Related
Jul 24, 2008
how do i download historical stock prices for approx a hundred stocks i.e what code would suffice?
View 9 Replies
View Related
Feb 9, 2006
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 9 Replies
View Related
Sep 15, 2007
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 9 Replies
View Related
Oct 16, 2012
I am trying to have a Vlookup Macro to add all the prices for a certain product in different tabs.
VB:
Private Sub CommandButton1_Click() 'Generate Info Button
Dim ws As Worksheet
Dim ISSUEPRICE As Integer
Dim RESULT As Integer
Dim sheet As Worksheet
For Each ws In ThisWorkbook.Worksheets
[Code] ......
View 3 Replies
View Related
Nov 21, 2007
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 10 Replies
View Related
Mar 5, 2009
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 5 Replies
View Related
May 5, 2009
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 12 Replies
View Related
Jan 4, 2006
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 11 Replies
View Related
Sep 30, 2013
I work with a broker that have an API function and all the prices goes directly to an Excel Sheet.
What I want is a way to keep track of this prices in a new sheet, as they change. Like a historical price.
Is there any way that excel can do it for me?
View 6 Replies
View Related
Dec 26, 2008
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 isn’t 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 9 Replies
View Related
Dec 7, 2009
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 9 Replies
View Related