Tracker
HOME    TRACKER    Excel

# 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.

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. 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 +

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%

How Do I Discount By % (percentage) Not The Amount
how to get the discount to work the way I need to. As is, It requires a person to put in the actual amount that I will discount the product for each product sold. I would like to simply key in the discount percentage. EX: 10% or 13%

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.

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?

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%?

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.

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 -of-day "till-up" 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.

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.

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)-25-80% i get the wrong answer
i know there has to be a different formula

Cost of visit= \$113.00

Q=Your have a co-payment 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.

Show Percentage Of Over-Due 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.

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.

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

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, 1-10, are assigned by a % bracket:

1-20% = 1
21-40% = 2
41-60% = 3
61-80% = 4
81-100% = 5
101-120% = 6
121-140% = 7
141-160% = 8
161-180% = 9
181-200% = 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.

Show Percentage Subtotals In Pivot Table
Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.

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.

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.

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.

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?

Auto Complete Items Off The Price List
I am trying to create an order form. i have a price list from my local hardware store that i want linked to my order form. i want my order form to autocomplete items off the price list. i have tried a few things but im stumped.

Price List Add 20-30% 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

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

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.

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.

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/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines

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 biggest-price 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

5050-4800 = 250

Calculate the difference in the last 2 sizes

4800-4700 = 100

Calculate the rounded up multiples

250/100=2.5 rounded up = 3...........

Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day
I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

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

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)
-------A---------B-----C--------D----------E----------------F
1----9.00am-----0-----0--------1----------2mins-----------9.02am
2----9.01am-----1-----0--------2----------15mins----------9.16am
3----9.05am-----0-----1--------1----------2mins-----------9.07am
4----9.06am-----1-----1--------?----------xmins-----------x.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.............

Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

Figuring An AVERAGE From 4361 Cells
I Need figuring an AVERAGE from 4361 cells... looking to get a blended rate on pricing.

I am not even sure if excel can avg that many cells.

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,

Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.

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.

If Statement: If The 2009 Price Is .50 Or Less Away From The 2008 Price, Bring Back "Check"
I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009
\$23.95 \$24.15

Using excel 2007

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.

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.

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

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.

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?

Sum Extended Price Without Extended Price Column
I have a unit price and a quantity. I want to be able to take the sum of the extended price without having to add a column for extended price. I don't want to just hide it, either.

Example attached.

Discount Formulas: Cells To Calculate \$40 X (100%-10%) = \$36
I would like to ask the formulas for this... for example:

\$35 .. discount 10% + 25%

instead of having so many cells to calculate \$40 x (100%-10%) = \$36
\$36 x (100% - 25%) = \$27

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.

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%.

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.

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 re-run 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
:=True
End Sub

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.

Show Sheets Names In A Drop Down List
Can a macro be provided to show the list of sheet names in a drop down list at cell B2?

Data Validation List Can We Show Colors
in a data validation list can we show colors,assume the following list as a data validation list

Item #
Orange =50
Apple =60
Banana=56
Mango = 40

in this list i want to give colors (as shown) to these two items

Make A Drop Down List Show The Picture
I am trying to do, is make a drop down list show the picture above it. I have tried to change everything, and I am sure I am just a hair away from getting it right. I got the spreadsheet to where it will pull up the "Picture #" in the appropriate box, and even had it delete/hide all my pictures (I say delete/hide because I couldn't get them back after I removed the coding I was working on).

Another nice function I would like would be a button, around cell B74, to erase all the picks and reset the sheet for the next season... it would have to have a prompt to verify the user was absolutely sure they wanted to do that though! But I don't know where to begin on that button issue. Can someone point me in the right direction?

The spreadsheet is too big to attch, so it's hosted at the link below. The sheets involved are NFL Survivor & PicTable. On sheet PicTable, the Names & Pic #'s have already been named PicTable for ease of coding.