Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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

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

View Replies!   View Related
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.

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

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

View Replies!   View Related
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.

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

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

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

View Replies!   View Related
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.

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

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

View Replies!   View Related
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?

View Replies!   View Related
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.

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

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

View Replies!   View Related
Conditional Format The List So That It Shows The Lowest Percentage In 'green' And The Highest In 'red'
I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.

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

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 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
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 Pop-up 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 macro--for a user shortcut--to display this--currently, 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
Show The Name Of Opened Workbooks In The List Box
I want to show the name of opened workbooks in the list box, but the following code does not work.

for example, I opened 'a1.xls' and 'a2.xls'. The listbox of 'main.xls' should show their file name.

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

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved