# Figuring List Price - Cost To Show Discount Percentage

Apr 25, 2006
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 9 Replies
ADVERTISEMENT
Apr 7, 2008

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 9 Replies
View Related
Nov 3, 2013

I want to create a spreadsheet for figuring the cost of some of my favorite recipes. Right now I have a table of basic ingredients (milk, sugar, etc) along with cost of same.

What I want to do is be able to set up a recipe that calls for "3 eggs, 1 sugar, 1 milk" and automatically figure the cost for me.

Ideally, I'd like this to be a "building block" spreadsheet - where I can have a recipe for "roux" (1 flour, 1 butter), and then a recipe for "broth" (1 chicken, 3 water), and then a recipe for "gravy" (1 roux, 2 broth) - where the spreadsheet is smart enough to do the math for me - and if the price of chicken goes up, I can change that in my basic list and it will carry down. And obviously, I want to be able to add ingredients and recipes easily.

View 3 Replies
View Related
Jul 7, 2008

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 9 Replies
View Related
Jul 14, 2014

How would I create a simple user form to display a price based off the category selected.

User types in List Price, then selects 1 of 4 discount categories, it then displays that price.

An added bonus would be copying that price to another sheet, but not necessary..

View 2 Replies
View Related
Dec 31, 2012

I have a price list with type & price, I want do a drop down list for type (this is solve), so when i select list at type, the cell I want it show price.

View 2 Replies
View Related
Dec 3, 2009

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 14 Replies
View Related
Apr 15, 2008

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 8 Replies
View Related
Jan 4, 2010

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 4 Replies
View Related
Dec 22, 2009

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 4 Replies
View Related
Jan 28, 2013

I have the following table in excel:

Price: Percentage:

$150 10%

$100 12%

$50 14%

I'm trying to use the percentage depending on what price I enter in a cell. For example; if I enter $135 then 10% will be used, if I enter $65 then 12% will be used, and if I enter $47 then 14% will be used.

View 3 Replies
View Related
Jun 23, 2010

Is there an excel formula that can calculate a proposed selling price by entering a desired profit margin percentage and knowing the cost of goods sold?

I know that gross profit is calcualted by subtracting the cost of goods sold from the selling price and that the gross profit margin is calculated by dividing gross profit by the selling price..but not coming up with a way to back out a selling price by just knowing the cost of goods sold and entering a desire gross profit margin?

View 9 Replies
View Related
Jun 20, 2008

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 9 Replies
View Related
Mar 8, 2012

I'm using Excel 2010 on a Windows 7 machine.

I have one sheet with a large list of dates and total prices.

I have a second sheet with a list of dates and unit prices.

I want to divide the total price in my first list by the unit price on my second list.

I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.

My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.

View 2 Replies
View Related
Aug 13, 2009

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 4 Replies
View Related
Jul 14, 2014

I need to make an automated way of extracting a list of IDs and the associated cost by a date range, say 6/1/2015 to 12/31/2015, plus the starting letter of the ID. I need the data to then fill a form where it can be sub-totaled. There are several additional columns of data that are not relevant so I know I can create a pivot table and then filter the data, but then I need to copy and paste the filtered data into the form. Is there a way to do this without me copy and pasting everytime?

I added a test worksheet to show the data and form.

View 6 Replies
View Related
Jul 20, 2006

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 5 Replies
View Related
Oct 7, 2008

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 2 Replies
View Related
Sep 27, 2006

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 8 Replies
View Related
Oct 22, 2007

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 4 Replies
View Related
Sep 16, 2009

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 9 Replies
View Related
May 23, 2013

I am trying to add 2 cell values together then show the total as a % of a value in a 3rd cell, however I also need it to allow for 0 values in the chosen cells without displaying an error message or it messes up the average formula elsewhere on the sheet?

View 2 Replies
View Related
Jul 31, 2014

I would like to know how to input 10% 25% ect then in another cell the graph bar growing till 100% mark is completed.

View 6 Replies
View Related
Feb 20, 2013

I am trying to calculate a percentage complete based on what a cells color is, the function must count the cells of a certain color (signifying in this case that something is complete for a particular "area") and distinguish that from cells in the same data range that aren't colored and calculate the percentage that are complete.

View 3 Replies
View Related
Jul 18, 2007

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 2 Replies
View Related
Sep 15, 2007

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 2 Replies
View Related
May 8, 2009

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 5 Replies
View Related
Jan 4, 2010

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 12 Replies
View Related
Feb 1, 2007

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 9 Replies
View Related
Apr 28, 2007

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 5 Replies
View Related