Formula To Calculate Price Based On Sheet2
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.
Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Price Based On Minimum
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
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
Calculate Selling Price
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
View Replies!
View Related
Copy Item Number And Calculate Price As Replenished
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
View Replies!
View Related
Formula To Calculate Based On Criteria
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column. The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis. I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.
View Replies!
View Related
Formula To Calculate A Fee Based On Performance
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance: KEYPerformance 2010,000 2120,000 2230,000 2340,000 I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges: KEYMin FeeFee 1Fee 2Fee 3Fee 4 201000.10.20.30.4 211000.10.20.30.4 221000.10.20.30.4 231000.10.20.30.4 For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows: Fee Range 10-9999 Fee Range 210000-19999 Fee Range 320000-29999 Fee Range 430000-99999999 So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest. How can this be combined into a lookup/range/low-high formula to extract the correct values????
View Replies!
View Related
Calculate Colum Total Based On Formula
I would like to have something that looks like a running total, but isn't. I have in a colomn 'rate' and after that a column for each month in a year. I would like to have at the bottom a total cost, so rate x hours. have a look at the picture. is there a formula for this? ratemayjunejuli $10 2,02,01,0 $12 2,03,00,0 $10 3,02,05,0 $15 4,02,53,0 total cost $134,0 $113,5 $105
View Replies!
View Related
Calculate Formula Based On Cell Criteria
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible? I have attached an example of what I am trying to do.
View Replies!
View Related
Mega Formula To Calculate Results Based On Several Conditions
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking. For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05) The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
View Replies!
View Related
Formula To Calculate Pay Based On Hours Worked
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example Hours ST OT Total Hrs Gross Pay 8 2 10 ? i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
View Replies!
View Related
Formula With If Condition: Calculate Bonus Based On Productivity
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.
View Replies!
View Related
Insert Rows Based On Criteria Match From Sheet2
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example: Sheet1 looks like this:...................
View Replies!
View Related
Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell
Basically a cell might have a number with 3 decimal places For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell. Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items. I included an example of what i mean in this excel file which should clear up any confusion.
View Replies!
View Related
Lookups For An Item's Price From A Price List
The analysis basically has 2 data components to it: The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time. The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased. What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below: 1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2) 2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least) 3) Pull the price for this "closest quantity" I have uploaded a worksheet showing the structure of that data. [url] Is there some VB code I need to do this, or can it just be a few simple formulas?
View Replies!
View Related
Function That Will Generate A Price Based On Three Fields
I'm a graphic designer and i'm trying to build a spreadsheet that will help me in doing my accounts. Ok, so i'm trying to set up a function that will generate a price based on three fields: Lamination:YES or NO Duplex: YES or NO Stock: Around 14 different stock options. I had it working using a nested IF but I ran out of the 7. I then tried using CONCATENATE using "&" to seperate but excel said the formula was too long.
View Replies!
View Related
Index Formula (find The Relevant Data And Return In To Sheet2)
I have a spreadsheet with multiple columns. (Sheet1) The heading row is my customer name. (Sheet1) My column are the dates. On another spreadsheet, I would like to find the customer in Sheet2 in Sheet1, then do an index to find the relevant data I need, returned in Sheet2. Sheet2: find customer "ABC" in Sheet1, if found, then find the date that also matches Sheet1 and Sheet2, and return data "apple" . sheet1 DATE ABC DEF 2007-04-01 £55.60 £61.13 2007-04-02 £56.33 £60.27 2007-04-03 £52.14 £62.58 2007-04-04 £51.69 £61.28 sheet2 CUSTOMER DATE RATE DEF 2007-04-04 This should be £61.28 ABC 2007-04-02 This should be £56.33
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
VLOOKUP Formula For Price Sheet
The task: create price sheet that calculates pricing based on 2 criteria - quantity, and production time. There are two worksheets. #1 is the main calculator, #2 is the price sheet, broken down with time (in hours) on the left column B, the quantity across the top in row 1. There are then boxes with different prices based on both the hours and quantity of products. On worksheet 1, I have specified quantity in C6, and time in C8. How do I pull a price in to F8 that calculates based off the time and quantity filled out on sheet 1?
View Replies!
View Related
Delete The Formula In The Amount The Unite Price Comes #value
(To keep things simple from left to right Column A-H) The Amount Column seems to be my problem, it has the formula =H98*B98 just a simple multiplication formula to get my unit price x my qty. When I delete the formula in the amount the unite price #value! error goes away. and all that is in the other error box is =IF(P98>0," per piece","") it just puts "per piece" in the box when something is typed. I have a vlookup formula in Column F (thank you VoG) =IF(ISNA(VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)),"",VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)) To pull prices from another worksheet.
View Replies!
View Related
Manually Enter % And Update Price (Formula)
I'd like to be able to add 10% to column D and have the prices in A, B, C change accordingly. Is there a formula for this? A B C D 2009 Distributor CASE Price2010 Distributor CASE Price2010 Distributor EACH PriceVariance from 2009 price135.00148.000.0592108.25100.000.040062.8875.006.250066.6096.008.0000 PS: Right now I have it set up working the opposite way, the prices are entered and my formula tells the user by what % the price has changed. The user wants to be able to tell the formula what % is desired and have the prices change accordingly. The formula I am using for the way column currently calculates is: =IF((G2=0),"",(((G2-F2)/F2)*1))
View Replies!
View Related
IF Formula Used To Price Life Insurance Policies
I price life insurance policies and need a formalu for the following new Fund: If the sum of the first two years of premium added to 10% of that total is equal to or less than 25% of the face amount, the case fits into the general parameter. Here is what I have, but it isn't working: =IF((U2+V2)*10%)+(U2+V2))
View Replies!
View Related
Building A Formula To Increase Price List 8%
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View Replies!
View Related
Small Business Formula For Price List
I need to complete my business price list. Basically it's an XL sheet with all my suppliers products listed, there price, my margins, and my retail price and bulk price. Currently I'm fiddling with my margins and the best way to implement a formula to reflect these margins. Rather than do it manually, is there anyway I can create a forumla for different gross margins to be set for a certain range of the suppliers goods. For example, any product I buy thats costs me between $0-$20 has a set margin of 1.44 (44%), or a product who's price ranges from $100-$200 has a set margin of 1.26 (26%) etc. This would cut out then need for me to manually check suppliers prices and change to margin accordingly, thus saving me much time.
View Replies!
View Related
Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current) Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines Sheet2 (has current material codes plus current pricing), has about 36000 lines I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
View Replies!
View Related
Looking For The Closest Price To A Reference Price
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price? I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances. I would also like to reference the source on the next column.
View Replies!
View Related
Multiple IF's And AND Inclusive Formula (all In One Cell) That Would Look At The Above Table And Depending Upon The Price Paid
.............................24............30............36 300014999..........9.00%.......11.00%.....12.00% 1500099999........9.50%.......11.50%.....12.50% 100000249999.....9.00%......11.00%.....13.00% I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
View Replies!
View Related
Making Average Buy Price And Average Sell Price
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
View Replies!
View Related
Creating A Mat Table Based On A Price Table
Part 1: Pricelist The user presses 1 of 3 macro buttons to access the price list. The buttons represent 1 of 3 parts of a construction project. The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro, depending on which button is pressed, hides all but the relevent quantity col & total col. Each job phase needs its own total & having it setup this way makes it easier to enter new items into the table (only have to enter the item once, rather than 3 times). The first html shows a simplied example of the "price list" described above (before & after the quantitiy & price cols are hidden). The user adjust the quantity & the sheet calculates the price total. Part 1: Material listThe material list is a list of items & quantities the user selected in the quantity col of Price list. This list is to be printed & given to the workers. It does not show price, only item & quantity. This list, unlike pricelist, must be visiablly broken down by job phase. I dont want the material list to be the price list repeated vertically on a sheet 3 times. This would take up too much space & require new items to be entered once in the pricelist & then 3 times in the mat list. I would rather the mat list be on a seperate sheet from price list & have a formula in cell matlistA1 that searches the phase 1 quantity col (then phase2 & 3) for the first #>0. When a #>0 is found, the formula would return an item name into the cell on matlist. Then the formula would run in matlistA2, & search for the next #>0. (& so on until a complete mat list table, broken up into phases is created) Sheet2 ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73 ..............................
View Replies!
View Related
Calculate Based On Text Value
I want to apply 4 conditions. Core ValueCurrent Pricenew Price a 400 b 600 c 300 d 300 If core value = a then new price should be current *2%, if core value=b, then new price should be current price times 2.2%, if core value = c than new price should be current price times 2.5% and if core value=d, than new price should be current price times 3%? also would i be able to do a formula if core value is either a or b, than new price should be current price times 2% and if core value is = to c or d, than new price should be current price *3%?
View Replies!
View Related
Calculate Points Based On Different Parameters
Something has X views, 10,000 for this example. I want: 1) The first 100 views to be worth 3 points each (=300 points) 2) The second 100 views to be worth 2 points each (=200 points) 3) The third 100 views to be worth 1 point each (=100 points) 4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points. How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
View Replies!
View Related
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 Percentage Based On The Number
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out. Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
View Replies!
View Related
Calculate Rank Based On Criteria
I want to calculate the sales rank of the customer whose CUSTOMERID is equal to my ID? Note that sales rank is what position the salesperson is in terms of DOLLAR VALUE OF SALES. Just a bit stuck on this. How do I calculate the rank? Is it a If statement or a Vlookup Question? The spreadsheet can be found here: http://www.megaupload.com/?d=5D0YCHIG (It's too big to be uploaded to the forum hosting).
View Replies!
View Related
Calculate Formulas Based On Top 20%
I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order. At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80. EXAMPLE: TOP 20% £1,330.00 £1,070.36 £997.50 £840.00 Employees - 4 SUM - £4,237.86 Median - £1,033.93............................
View Replies!
View Related
Calculate Percentage Based On Criteria
I have events in column 1,in my sheet there are about 800 events. In col 2. each contestant has a label,blue(b),green(g) or red(r) In column 3 I want to print the % of blue + green of the total for each event, e.g first event in attached sheet would show 77.7% down to row 10 in col 3 etc.
View Replies!
View Related
Calculate An If Value Based On A Number Of Conditions
I am trying to create on excel order form. I want customers to be able to input the item # (a range from 1 to 12), then I want the to price to be calculated based on the item # they input. For example. If they choose item #2 in A6 then the price in F6 will be recorded as $8.00. (the price would change for each item # they input). the formula I started out with was: =IF((A6=1),"$8.00") this worked for me if A6 did in fact equal 1. So I tried adding this equation to the formula. =IF((A6=1),"$8.00")*OR((A6=2),"$7.00")....this would continue on. I even pressed "command return" after the statement as if I was entering an array formula. I got the error #VALUE!
View Replies!
View Related
Calculate Wages Based On Criteria
I am working on my companies tax reports for the first time. The wages for unemployement and FUTA do not calculate IF the total wages are over a certain amount. Im trying to get the formula to see if the total wages are over $7000 then dont calculate. Ive tried several different things from the links, but still can't make it work. total wages are $7800, so i only need to calculate it on $7000 and then not calculate it again for the next quarter.
View Replies!
View Related
|
TRACKER
