Add A Margin Calculation To A Formula?
Apr 20, 2014
I put my margin in G6 of the data sheet and the cost are the numbers on the Mail Rates sheet.
cost/(1-G6) and then round result up to next .25
=VLOOKUP(Calculator!E6,'Mail Rates'!A2:D32,Calculator!D6+1,FALSE)
I am using the vlookup above to bring the mail rates to a calculator. I want to add a margin to the mail rates before they reach the calculator.
The margin amount is in G6 of the "data" sheet and the margin formula is "mail rate"/(1-G6)
I would then like to round the result up to the next .25 dollar
View 3 Replies
ADVERTISEMENT
Dec 1, 2008
I need to work out the profit margin from goods i sell.
I have cost A2
Pack size A3
Net cost A4
Tax A5
Gross price A6
I need to work out the margin between A5 and A6.
View 10 Replies
View Related
Oct 8, 2008
I'm trying to build a formula to form a price-list. I have some basic prices from a supplier and want to build my prices with a simple rule: the higher the basic price is (column A), the lower my profit margin (in %) should be (column B). Example:
Basic value is $50, my price is $75 (50% margin)
Basic value is $100, my price is $130 (30% margin)
Basic value is $150, my price is $172,5 (15% margin)
And so on...
I forgot most of what I've learned on Excel at my university (long time ago...), so I tried to do it by using simple thresholds, with "if" function:
View 5 Replies
View Related
Jan 8, 2014
So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?
So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.
View 5 Replies
View Related
Aug 15, 2006
I have events in column1 and ratings in column 2. The ratings are not sorted.I column 3 I want print the difference between the highest rating in event 1 and the second highest e.g. in the attached sheet column 3,rows 2 to 7 will contain 13.9. Similiarly for event 2 etc down my full sheet of 1200 events.
View 9 Replies
View Related
Jun 1, 2013
Using VBA, how would I move an existing shape object over to the righthand margin?
I suppose what I need is something like:
VB:
ActiveSheet.Shapes("Picture 1").Left = activesheet.margin.right - ActiveSheet.Shapes.("Picture 1").Width
But how do I find "activesheet.margins.right"?
View 1 Replies
View Related
Jan 30, 2008
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...
however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!
one such formula is:
=IF(AND(AC11"No Calc",AC11>=1,AC11
View 9 Replies
View Related
Oct 17, 2013
This is the Case:
I Produce something with a €2000 cost price. this is variable.
I Sell it for lets say € 2500,- to someone this is also a variable.
That's makes a Profit of € 500,- on the sale. This is 125% revenue
Until here, no problem using a spreadsheet. but now the difficult part.
I have to split of the 25% margin over 2 persons. This has to be done as follow:
a. The first slice is 2,25 for Mr A
b. The second slice is 15,00 for MR B
c. The Third Slice is 2,75 for mr A
This divides 20 of the 25 % over 2 persons. Still 5% margin remains
The last 5 is devided as follow:
d. 85% for MR A
e. 15% for MR B
So in this case:
MR A receives: a:€45 + c:€55+ d:€85 = €185,-
MR B receives: b:€250 + e:€15 = €265,-
The tricky part is when the margin is less than 20%.
for example; If the margin is 14%. than only slice a. and a part of slice b. an be paid off. so in that case;
MR A receives: a:€45 + c:€0+ d:€0 = €45,-
MR B receives: b:€230 + e:€0 = €230,-
Any formula which calculates the revenue for MR A and B automatically based on only the cost and selling price.
A B
1 Cost price - €2000
2 Selling Price - €2500
3 MR A - €185,-
4 MR B -€265,-
View 1 Replies
View Related
May 27, 2008
I have the margin and I have the sale price. How do I figure out the cost?
View 12 Replies
View Related
Mar 1, 2012
I'm trying to calculate a weighted average gross margin but am having difficulty because I've instances of positive cost of sales and negative revenue. On a line by line basis I've had no problems calculating the margin but when it comes to calculating the weighted average margin as a whole I come unstuck. Looking at the data below (A1:D14) you can see that the margin should be somewhere between 90-99%.
Total RevenueCost of SalesGross ProfitGross Profit %4,805(449)4,35690.6%4,992(330)4,66293.4%3,467(246)3,22192.9%4,974(317)4,65893.6%3,873(130)3,74396.6
%3,756(258)3,49893.1%3,955(128)3,82796.8%2,872(59)2,81397.9%1,401(1)1,40099.9
%(10,987)21,45810,47195.3%3,042(172)2,87094.3%2,748(40)2,70998.6%2,682(172)2,51093.6%
View 4 Replies
View Related
Sep 10, 2008
I have a table with daily sale entries and margins. I want a formula to calculate my sale and margin between certain hours.
The original data table is below.
Data table:
ABCDEF1DateHoursQuantityUNIT PRICESALEMARGIN201/07/0810:351-4,00-4,00-4,00301/07/0810:35122,0022,0010,05401/07/0810:5016,006,001,80501/07/0810:5016,006,001,80601/07/0811:0313,803,802,00701/07/0811:0316,806,801,67801/07/0811:0415,005,002,64901/07/0811:0733,339,993,901001/07/0811:0735,0015,008,071101/07/0811:1016,006,003,971201/07/0811:1115,605,602,971301/07/0811:1316,006,003,971401/07/0811:1412,602,601,831501/07/0811:1412,602,601,781601/07/0811:21111,6011,606,401701/07/0811:2226,0012,006,821801/07/0811:2413,503,501,471901/07/0811:28111,6011,603,952001/07/0811:4115,605,602,902101/07/0811:4115,605,602,902201/07/0811:42118,8018,806,722301/07/0811:4417,807,801,612401/07/0811:4523,507,002,942501/07/0811:4712,002,001,532601/07/0812:0113,203,201,84
The results I need:
ABCD1DATESALE BETWEENSALEMARGIN201/07/200810:00 TO 11:0030,009,65301/07/200811:00 TO 12:00148,8970,04
View 9 Replies
View Related
Nov 16, 2011
I am trying to set up a spreadsheet to calculate margin on stock positions.
There are 3 tiers, based on number of shares. My example:
400 shares in held in total.
1st 200 shs margin at 10%.
next 100 shs margin at 20%.
everything above 300 shs (ie 100) margin at 30%.
The 400 shs may be made up in any number of ways, (ie lots of smaller amounts) and i need a formula to give me the margin amount on each position, flexible enough for when positions are added / removed.
I've been looking at SUMPRODUCT but can only seem to make it work for the total, not each individual position.
View 9 Replies
View Related
Jan 25, 2005
I have a price sheet for my store in Excel. I want to make the spreadsheet easy for my sales people to use to calculate differing profit levels. I'd like to embed a drop down box at the top (I can do that part) that they can select, say, 10%...20%...30% markup. I know how to do that basic formula. I don't understand how to link that formula to a number selected in a drop down box. I don't know how to make the prices change based on the value selected in the drop down box. I can't do vlookup because it's about 1500 lines long. Also, it doesn't have to be drop down box based--that's just the idea in my head. I tried just making a macro that would run when they hit the button, but when the macro runs it switches the focus back to the top, very annoying if you were looking at prices on cell D811.
View 7 Replies
View Related
Nov 29, 2012
I am looking for a formula which will calculate a sell price with 20% profit after taxes and costs. I need it to factor in a fixed shipping cost (eg £10), a 7.5% Amazon fee of the sell price, then 20% tax of the sell price. I currently have a formula which essentially does this but as I'm not good with Excel it requires me to input all the values every time which is not practical considering our large inventory.
View 3 Replies
View Related
Nov 17, 2009
I have a section of my program that asks for board scores (point value is 6-10 in increments of .5) I have set up a data validation so that users can only enter those specific values. Problem is, none of the values can be more than 1 point from each other. (the part I have yet to figure out)
Example, if the first value is a 6.5 the second is 7.0 and the third is a 9.0, I need the data validation to display an error because the 9.0 is beyond the one point spread.
Summary: Display an error message if any of the points are more than 1.0 point from another.
View 7 Replies
View Related
Oct 7, 2009
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.
I've got a formula
=IF(COUNTA(A1)=1,TODAY(),"")
When something is typed in Cell A1 the cell with the formula will input todays date.
Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View 6 Replies
View Related
Feb 28, 2012
Looking for a formula that can account for a minimum value.
The calculation is as follows 1.22*(x)+8=(y)
(y) must be greater than or equal to 20.
View 7 Replies
View Related
Jun 26, 2013
I am trying to calculate the present value of a terminal period in Excel. The manual calculation and excel PV function are off by about $98,000. Both calculations are using the same capitalization rate and terminal life. My PV Excel formula is as follows: =PV(discount rate-growth rate,remaining term (years),-terminal cash flow,,0)*present value factor in last year of cash flow) What is causing the difference in values? Is there something in the Excel formula that is causing the difference?
View 1 Replies
View Related
Jul 8, 2013
I have data regarding the percentage of QC for the team.
I need to find out the individual's average QC scores from the list already available.
The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage
View 3 Replies
View Related
Jun 15, 2007
is a formula to work out how many paid sick days an employee is entitled to based on their employment start date
Up to 6 months service the entitlement is 5 days
After 6 months it is 20
After 1 year 40
After 2 years 60
After 3 years 80
View 9 Replies
View Related
Jul 14, 2009
I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details....
The formula currently being used is... =IF(G50=E50, H50-F50+(H50
View 9 Replies
View Related
May 8, 2006
I am putting together a formula that will be able to choose the calculation. There is a couple of choices with the spreadsheet registry, non registry, and interfile. They have different standards per hour 56, 40 and so on. What I have so far is not working. It is =volume/(time*standard),Volume/(time*standard) with each standard being different to bring the correct percentage. How can I put the two to three formulas in one cell
View 2 Replies
View Related
Dec 14, 2006
I am getting a multiplication error in excel when I multiply 1796 X 156 the answer should be 280,176. However, I am getting an answer of 280,187 any suggestions? I am working with mutiple cells ect.
View 9 Replies
View Related
Apr 14, 2007
I have a simple calculation - Say 0-6. What i need to do is, if the value is <7 place a 10 in the cell, or if the the value is <12 place a 5 in the cell, or,
lastly if the value is <21.5 place a 0 in the cell. Is this at all possible - as i can only do it with conditional formatting using colours at present
View 7 Replies
View Related
May 11, 2007
I have attached a time card that needs a formula to calculate the time for the high lighted areas.
In+out-in+out= total hours.
View 4 Replies
View Related
Aug 2, 2007
I'm trying to use the if condition formula however it doesn't give me the calculation?
=if(A2<20%,"B2/.80", A2>20%,"c2*.02)
My division and multiplying functions do not work or it doesn't calculate for me?
View 6 Replies
View Related
Oct 9, 2007
how can i create a formula by 3 variable?( power form: y=(x^a)*(v^b)*(g^k)*p that y,x,v,g are known and other parameter are unknown.)
View 2 Replies
View Related
Feb 3, 2014
I don't want to appear too expensive or give my products away. If I have a part that costs me £20.00 plus vat i.e £24.00. I have to include carriage £5.00 plus vat i.e £6.00. Ebay fees for car parts including PayPal is 10%. How much would I charge to retain 20% percent after all fees and charges and what formula would I use to make the calculator.
View 2 Replies
View Related
Mar 12, 2009
I want to restrict the value entered on a sales sheet to force the value to be over 15% margin. In column M you enter a value in column N it report the margin. I want to force the value in M to give a minimum 15% in column N or report an error.
View 3 Replies
View Related
Sep 12, 2013
I need to develope a macro for Excel to calculate desired profit margin depending on our cost of each item. Here's the scenario.
A1: landing cost
If 0
View 4 Replies
View Related
