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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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. 110 are £12.50 each, but 1020 are £10 each and 2030 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
Figuring A Percentage
Store A sells something for 100 Store B sells the same item for 125 Store B is 25% higher than A how would a formula be written to show that? Store B / Store A = 1.25 How do I get it to 25%
View Replies!
View Related
Calculate X Percentage Of Cost
I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.
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
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 autofilling, 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
Show Only Amounts Within Price Range
I have a table of information as follows: CLIENT BILLINGS INCOMEMARGIN CLIENT 1 13,740,719 541,5743.9% CLIENT 2 2,524,535 59,799 2.4% CLIENT 3 349,125 17,085 4.9% I need to be able to show amounts less than £1million which is easy enough in an IF statement but then I need to be able to show amounts between £1million and £5million as a seperate block.
View Replies!
View Related
Efficient Entry Of Data & Automatically Have Their Price Show In Adjacent Cell
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end ofday "tillup" process to reconcile purchases and services rendered aginst cash and cheques received. Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price. All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
View Replies!
View Related
Show Percentage Of OverDue Dates
I have a spreadsheet that shows when individuals completed different events. The events are on top and the names are down the side. There are dates in the middle. I would like to know if it's possible to show a percentage of dates that are current so take the number of fields with current dates and divide them by total number of fields. I would also like to find a percentag of events complete by each individual.
View Replies!
View Related
Percentage Calculation (want To Show % Wearing)
I am a Safety representative and on a monthly basis I have to check for seat belt usage. I can not, for the life of me, figure this out. I have attached an example. In the example out of 22 vehicles there were 2 individuals not wearing seat belts. So cell J4 should be 91% I can calculate % not wearing with [=sum(D4:E4,H4:I4)/SUM(B4:I4)] but I want to show % wearing.
View Replies!
View Related
If Percentage Calculation Negative Show Zero
i am having a hard time in my microsoft excel class for the life of me i cannot think of a formula to answer the question below every time i type in =(c3)2580% i get the wrong answer i know there has to be a different formula Cost of visit= $113.00 Q=Your have a copayment of $25.00 and the insurance will pay 80% of the remaining cost. Create a formula for the visit that will calculate the total amount you will have to pay.
View Replies!
View Related
Show Percentage Difference Between 2 Columns
I have a spreadsheet which has a number of columns populated with a day number and below each day number there are some numbers. What I would like to do is to compare the values for Day1 with Day2, Day 2 with Day 3 etc. Is there a smart way to do this using VBA? I am enclosing an example to show what I would like the macro to do.
View Replies!
View Related
Price List
i have a some detail in the tabular form with different criteria as size, colour ,purity,cut ,by combining all these i have price list in a tabular form , if i want to intersect all this and find the price how can i do so
View Replies!
View Related
Show Numbers Based On Percentage In Cell
I have a rather large report full of data, 10+ columns contain a RANKING column, and these ranks, 110, are assigned by a % bracket: 120% = 1 2140% = 2 4160% = 3 6180% = 4 81100% = 5 101120% = 6 121140% = 7 141160% = 8 161180% = 9 181200% = 10 There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.
View Replies!
View Related
Price List Lookup
I have a price list Width/Drop 100013001600190022002500280031003400370040004300460049005200 850 1150 1450 1750 2050 2350 2650 2950 3250 3550 3850 I need to index given : My ranges are named Width and drop and List. Rules are anything above largest drop/width must return 0. anything below lowest width/drop will be the lowest listed anything inbetween will choose the value >= to the value. lookup is returning the closest, and my other formula fell foul of nesting, i am working in 2007 but it is targeted at XP/2002.
View Replies!
View Related
Price List Add 2030% And End In 99cents
I get a price list from my distributor, my scale I use for my store is anything below $10 (from my distributor price) gets 30% markup, then anything above $10 gets 20% markup. How would I be able to function this into the list I receive in excel so I can export the prices directly to my store of what the prices should be in my store. Also it would be a plus if at the end I can get it to end in 99 cents. Thank you look forward to your response! I am using Excel 2003
View Replies!
View Related
Update Price List
I have a price list with part numbers in Column E and prices in Column C. I want to update the prices from a master list that has part numbers in Column D and prices in Column H and then make only the updated prices bold. Currently the master list is in a different workbook, but if I need to, I can copy and past the master sheet into the same workbook as the sheet I want to update.
View Replies!
View Related
Add 5% To Many Price List's Macro
I have around 150 separate price list files and i would like to create a macro to findformat currency then copy 1.05 and use Edit  Paste Special (value, multiply)...... then round up or down to nearest cents .567 would roundup to .57 The findformat and Paste Special will work when i manually do it.... but when i record as macro it will not work when played back...... Here is the code it records... code does not include the roundup part. I dont know how to do that. Range("M3").Select ActiveCell.FormulaR1C1 = "1.05" Range("M3").Select Selection.Copy Application.FindFormat.NumberFormat = "$#,##0.00" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Range("M3").Select Application.CutCopyMode = False Selection.ClearContents End Sub
View Replies!
View Related
VLookup: Old Price List Update
A B C D 1 123 1.99 123 2.09 2 124 3.99 124 4.09 New prices arrived as shown in column C (code) & D (price) I want my old price list updated A (code) and B (old price) Can VLookup use the new data and replace B with information from column D.
View Replies!
View Related
Multiple Pictures In A Price List
Hi, I've got a price list with 2500 different lines which the boss has decided need a picture against each one (the guys a legend!!). Can anyone help me with a macro that will look up the code in Column A, then add .jpg on the end, and insert a picture into Column J?
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
Extending Vlookup: Price List To Cover Up To 10,000 Lines
However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excelwork...hoicesum.html. Need to extend price list to cover up to 10,000 lines
View Replies!
View Related
Price List Lookups And Additonal Calculations For Out Of Range Values
The sheet has a price list (I attached the sheet). its a width x height(drop) format. If width or height <= minimum width/height then use the minimum listed. if width or height > minimum <=maximum then lookup in table next heightest value. here is the complication. any oversized items are priced as roundup((size biggest size) / (biggest  second biggest size),0) * ( price of biggestprice of second biggest). so if my widths are 4600 4700 4800 10 25 35 and I am pricing 5050 I would do : calculate howmuch its oversize 50504800 = 250 Calculate the difference in the last 2 sizes 48004700 = 100 Calculate the rounded up multiples 250/100=2.5 rounded up = 3...........
View Replies!
View Related
Figuring Percent %
This is what I have Rate Hours =basePay plus 6% plus 7.1% total $50.00 10 $500.00 $530.00 $567.63 $567.63 What i want to have is one cell that I can Total everything. I want my spread sheet to display just rate, hours total I am having troule making the formula to display everything in the total cell
View Replies!
View Related
Figuring Out Which Function To Use For Scenario
Need constructing a formula and im not too sure which function to use. Im also not sure if its even possible or im missing a required column to help make it possible. (arrival Time)(c1)(c2)(served by)(time served)(exit time) ABCDEF 19.00am0012mins9.02am 29.01am10215mins9.16am 39.05am0112mins9.07am 49.06am11?xminsx.xxam Column A = Arrival time Column B = Number of customers in Counter #1 as of arrival time Column C = Number of customers in Counter #2 as of arrival time Column D = to be Served By Counter: Column E = Mins being served before exiting the counter/que Column F = exit time rows = customers entering a que.............
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(DataTarget)),ABS(DataTarget),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
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
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
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
Track The Levels: Club Is 0 To 19 Points And Yields An 11% Discount
I have a 4level 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
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
Show Popup List Of Sheet Names
when i open a xls, i would like to Display the Vertical Menu Of Worksheets (at the bottom left of the tab names) this worksheet has many sheets and i find this an easy way to navigate... (also, how can i write a macrofor a user shortcutto display thiscurrently, i hover my mouse pointer over the bottom left tab navigation object and right click to bring up the vertical display of sheet names...)
View Replies!
View Related
Drop Down List To Show Worksheet
I have a Workbook with a dropdownlist on it. The dropdown list has some names on it each name will be a refference to another sheet, ie when name Collins Jim is clicked on the drop down list the Jim Collins sheet will appear. I have attached a sample sheet to show you waht i mean.
View Replies!
View Related
Filter List To Only Show Uniques
I have a list with many duplicate names in it, from which I only wanted 1 instance of each name, to be placed in another sheet. So, I recorded a macro that selects, from the sheet called "Data", column E (the column with the relevant list), copy/paste's it into sheet 3 in column A, selects the new column, and runs an Advanced Filter command on it, to identify and display all the unique names in it in the same place. It ran just fine when I recorded it. But then, when I deleted the data created by the macro and tried to rerun it, I got random data. I found that whenever I click on a different cell, it provides a different output. I only want it to select Column E, and copy paste that, not other random columns depending on which cell is currently selected. This is my Sub Fetch_Unique_Names() Sheets("Data").Select ActiveCell.Range("A1").Select ActiveCell.Range("E1:E2200").Select Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Range("A1:A2176").AdvancedFilter Action:=xlFilterInPlace, Unique _ :=True End Sub
View Replies!
View Related
Show List Depending On Drop Down Selection
Im currently creating a spreadsheet for my work, with common problems and solutions to one of our websites. When the spreadsheet is opened, the user will click a button depending on what sort of problem he/she is having. In the example attached, if the user clicks on "make payments" a form will appear. This will have a list of problems. if the user choses problem 1 from the drop down list, then I would like the list of solutions to problem 1(from the MPData sheet) to show in the box below. The same goes for problem 2, problem 3 etc.
View Replies!
View Related
