Free Microsoft Excel 2013 Quick Reference

Formula for Calculating List Price using Discount Percentage and Net Price

Hello, I have a net price (C4) from a supplier along with a percentage discount (D4) and want to calculate the starting list price. Can you help me with a formula for this? Thank you.


Post your answer or comment

comments powered by Disqus
I have a spreadsheet that calculates prices on a shopping list, such as price ea x quantity = purch price. I have some names for deals, such as B1G1F (buy one get one free), 50%, etc, which is factored into the formula for purchase price as needed. I am in need of a math formula (I can write out the formula in Excel) that would calculate a Buy One Get One 50% in BOTH a scenario with multiples of 2 quantity as well as a quantity of 1. The formula I have now works fine quantities of 2 and up. But if I have a quantity of 1 (there are reasons sometimes I need to use a 1 quantity), I am not getting the right answer. If I fix it to work on 1, it doesn't work on 2+. The basic math I'm using for the 2 and up is, (Quantity/2 x Price) + ((Quantity/2) x (Price/2)).

Thanks in advance!

I am trying to write a formula that will calculate a price based upon a tiered pricing system. The tiering id this:

Fee is 1.00% of amount up to 1,000,000. The next 4,000,000 (ie 1,000,001 million thru 5 million) is charged at 0.70%, and the amounts over 5 million are charged at 0.60%.

So, for example if the figure is 8,000,000 then the fee would be:

1,000,000 at 1.00% = 10,000
4,000,000 at 0.70% = 28,000
3,000,000 at 0.60% = 18,000
Total 56,000

I want to write a formula that will reference an input cell such that if I input 8,000,000, the formula will calculate $56,000.

I have tried to do it with an IF formula and just can't make it work.

I need a one cell formula that calculates a price based on the following
conditions

Items costing the Contractor $50 or less are marked up 100% witha
profit margin on the selling price of 50%
items costing 50.01 to 100 are marked up 80% with a profit margin on
selling price of 44.44%
items costing over 100 are marked up 60% with a profit margin on the
selling price of 37.50%

--
nander
------------------------------------------------------------------------
nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=393050

Formula for Calculating (Accrual) Off Time (PTO)
I need to develop a tracking spreadsheet (for 18 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters:

Employees who have worked less than 1 year with the company:
- 10 days
Employees who have worked at least 1 year with the company:
- 12 days
Employees who have worked at least 2 year with the company:
- 13 days
Employees who have worked at least 3 year with the company:
- 14 days
Employees who have worked at least 4 year with the company:
- 15 days
Employees who have worked at least 5 year+ with the company:
- each year will add 1 day until reach the max 20 days

In addition I need to be able to deduct vacation time used. Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

I need a one cell formula that calculates a price based on the following conditions

Items costing the Contractor $50 or less are marked up 100% witha profit margin on the selling price of 50%
items costing 50.01 to 100 are marked up 80% with a profit margin on selling price of 44.44%
items costing over 100 are marked up 60% with a profit margin on the selling price of 37.50%

I need help with a formula for calculating sales price of a house. I need to calculate the price after taking into account all construction costs and also sales commissions and based on a target profit margin. The only problem I am having is that sales commissions are based on the sales price which means I end up with a circular argument and the profit is incorrect. I know this is probably a simple formula and I would be very grateful for any advice.
Thanks

DOES ANY ONY HAVE THE FORMULA FOR CALCULATING MIN MAX LEVELS FOR INVENTORY
CONTROL

Hello,

I finally figured out the formula to calculate vacation time based on anniversary date for full-time employees.

Now, within the same sheet, I need to calculate vacation time based on anniversary date AND hours worked for part-time employees.

Here is the criteria:

0-3 years of service 1 hour for each 20 hours worked with a maximum of 4 hours
3-15 years of service 1 hour for each 13 hours worked with a maximum of 6 hours
15+ years of service 1 hour for each 20 hours worked with a maximum of 8 hours

Example: A part-time employee works 50 hours per pay period and his start date was 8/25/2002. Manually calculating, he would earn 3.8 biweekly
A part-time employee works 32 hours per pay period and his start date was 6/19/2011. He would earn 1.6 biweekly

Thanks!

I am putting together a time sheet, and I am trying to figure out a formula for calculating overtime hours. Anything over 8 hours is considered overtime, and I would like those hours to appear in a different cell.

In my example (attached), you can see that the hours for 1/20 are over 8 hours, and 10 hours is showing in the "Total Regular Hours" column. I would like for all hours entered in this column to stop at 8, and any hours over 8 would show in the "Total Overtime Hours" column. (In this case, there should be an '8' in the "Total Regular Hours" column and a '2' in the "Total Overtime Hours" column.)

I've tried a few different options, but I can't figure out what the formula would be to achieve this result. Any help would be greatly appreciated!Timesheet Worksheet.xls

List Price less Disc1 less Disc2 equals Net Price. Sell price less net
price equal profit divided by sell price equal gross margin percentage.

$10 less 50% less 10% =$4.50. Sell for $12.50. $6.00 less
$4.50 =$1.50. $1.50/6.00 = 25% GP.

All of these figures are in cells of their own. Cannot get this to
calculate correcly.

Please help! Urgent

I was trying to create a small billing software using Excel... I tried myself and ended upto this level.... I used some dynamic dependent list to auto populate the "Particulars" field and its "Unit Price" field. Now i want to calculate the "Total Price" by multiplying the "Unit Price" with ""Quantity".

I am in the final stage guys, but the problem is When i use formula to calculate this, the contents will be cleared including the formula when i click on "New Invoice". i am looking for VBA code from the experts to calculate the "Total Price", by automatically taking the values of "Unit price" and "Quantity". I dont want to do this with any sorta formulas, I am only looking for the VB code, that helps fix this issue and I dont want to calculate the "Total Price" manually.

Find the Excel file that i am dealing with in the attachment...

Thanks in advance...

I have a problem using goal seek! Enclosed file is showing our GM calculations for a given product. To calculate our GM a dicount and bonus is deducted from the Gross price and freight costs (calculated as a percentage of Gross price after discount) are added to the purchase price. What I would like is to be able to calculate the Gross price (yellow cell) when all the already known data (green cells) are keyed in and the GM is the target (red cell). This means I would like to key in a new GM, f.ex. 45%, and then receive the new Gross Price.....

Thanks a lot for your help in advance

Hi folks,

I'm new to the forum and not too hot with Excel either so please be patient with me.

I am after advise on creating a formula for creating percentages.

Basically this is for a chemicals spreadsheet.

What I have is a chemical - lets call it Chem 1

This will be made up from various raw materials so for maybe this example it's made up of 2 raws - let's say Raw 1 which is 60% of the finished product and Raw 2 which is 40%

So, what i need to be able to do is to enter into a cell my total order for Chem 1 in kg's, say 200kg and have it calculate into two seperate cells for the raws, how much i'm using of each raw (in this case 120kg & 80kg) and then to deduct it from a stock which would be pre-determined.

So, thank you very much indeed in advance for your help - hopefully the above makes sense.

Kind regards

Ian

Hi,
Need help with an excel formula to work out cost price for an item after applying a discount. I am only new to this so if anyone can help it would be much appriciated.
I have following cells which have the data I need to look at to get final cost.
E10 - Has cost price per meter before discount
D10 - Has size in meters of lenth of metal
F10 - Has discount figure (46%)
G16 - Is where I want discounted price to appear.
(I need a formula to multiply cost price x size of length of metal, minus the discount figure).

Tks Mike

A search of the Mr.Excel archives turned up a formula for calculating the running average for a row of data by only averaging the last N entries. That formula is:

=AVERAGE(OFFSET(INDEX($C4:$AE4,,COUNT($C$4:$AE$4)),,-2,1,3))

Where data exists in 23 columns (C through AE) of row 4. It calculates the average using only the last 3 entries. this is only the case if data is filled in every column.

I am looking for a formula to accomplish the same thing. Automatically calculate a running average using the last 3 entries. However, the row of data I am using doesn't always contain entries in every column. An example:

Columns A-H contain the following (B=Blanks)
33B5B7B6

I am looking for (5+7+6)/3 = 6, but on a running basis (as the next number is added, the value in D (5) would be dropped. The formula listed above returns and answer of 5 when applied to the data set above.

I need help with formulas to calculate the percentage of variance
between planned miles and actual miles driven by drivers at several
different locations. I am given the miles per location daily. I need
to calculate weekly percentage per location. Then YTD percentage per
local, then total for all locals, then YTD for all locals. Can someone
help me?

--
Lori Lynn
------------------------------------------------------------------------
Lori Lynn's Profile: http://www.excelforum.com/member.php...o&userid=25565
View this thread: http://www.excelforum.com/showthread...hreadid=389974

I built a spreadsheet to calculate hours worked; I did it over a couple of weeks, reading and learning as I went. During the time I was doing this, somehow I ended up with 2 different formula for calculating hours worked...these are:

=IF(N31>O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25))

and

=IF(N32>O32,CEILING(("24:00"-N32+O32),7.25),MIN((O32-N32)*24,7.25))

where N and O are the start & finsh times respectively.

Both seem to calculate properly, but does anyone know which is the better formula to use...of does it matter ? Thanks.

I have a worksheet of property latitude and longitudes along with another sheet of the county centroid latitude and longitude. Along with each property record is a price field that will also factor in the results.

For each county, I have to calculate the distance from the county centroid to each property and return the prices for the top 5 closest counties (where the distance is greater than 0). From there, I need to calculate a median on the prices.

I am using this formula for calculating the distance between two lats and lons:

In this example, the lat1, lon1 values would be coming from the centroid of the county, and the lat2, lon2 values would be
the property (but I need to basically run this calculation on a range of properties).

Here's an example

What is the formula I would use for calculating dates 3 mos. out

ex.

April 17, 2006 July 17, 2006 October, 17, 2006

Hello,
I am trying to figure out the formula for calculating retail price if I have the cost and margin (%)/

Example:
cost is $10.00 (known figure)
Markup is : 40% (known figure)
Retail should be: 16.66

Thanks JR

Hi,

I am doing a list of selling prices for producrts which have the same ,markup. Basically I have done the cost price in 1 column and the markup and VAT rate in 2 seperate cells (so that I can change without changing all the formulas).

How can i do the formula to for example a cost price of €50.00 is marked up by 35% and Vat is added on it?

Thanks

Ian

I need to calculate inventory forward weeks cover for the coming months. Based off of recent forecasts, I have the month end inventory positions and the Cost of Goods (cogs) by week also.

Can someone guide me on how to use a formula to calculate the forward weeks cover (i.e. how many forward weeks cogs can fit into the month end inventory position).

Hopefully I've explained myself correctly.

Thanks in advance.

I need help with formulas to calculate the percentage of variance between planned miles and actual miles driven by drivers at several different locations. I am given the miles per location daily. I need to calculate weekly percentage per location. Then YTD percentage per local, then total for all locals, then YTD for all locals. Can someone help me?

Hi Guys,
I'm trying to calculate a monthly leave accrual formula for staff on a monthwise table.

In the sample file attached, John has joined on 01-NOV-2010 so his leave accrual will start from Nov @ 2.5 per month. But I would like to have a formula that returns accrual as zero for the months prior to his joining.

Similarly, Mary has joined on 15-Dec-2010 so in the joining month of Dec2010 her leave accrual will be (2.5/31 x 17 days of service in Dec) and for the following months it will be 2.5 per month

Like Ali, who joined on 17-Feb-2011 wil have the zero accruals till the month of Jan and in Feb it will be (2.5/28 x 12days of service in Feb )

I hope the sample is clear. Any remote soultions to build on will be helpful.
Thanks


No luck finding an answer? You could always try Google.