Formula To Decrease A Margin, In Connection With Increasing Basic Value
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
ADVERTISEMENT
Aug 24, 2006
I’m trying to make this:
Amount of money: 1000 (changeable)
Beginning # of units: 0,1 (changeable)
Delta: 200 (changeable)
Rate of decrease: 50% (changeable)
0,1 unit should be added for every 200$ increase (1000+200=1200; 1200+200=1400; 1400+500=1900; 1900+256=2156 etc)
0,1 unit should be taken away after decreasing by100$ (because the rate of decrease = 50% (changeable))
After increasing of 1000 + 200, quantity of units should also increase for 0,1, so it should be 0,2 (1200), 0,3(1400), 0,4(1600)….. Amount of money can increase not only by 200, but for any sum. If that sum is (for example) 500, we should increase by 0,2 etc.
But when this some decreasing we are using rate of decrease (for example 50%, (changeable):
0,4 units – 1600
0,3 units – 1500
0,2 units – 1400
0,1 unit – 1300
View 2 Replies
View Related
Jun 10, 2013
i have been working on 2 sheets receiving and selling item and QTY, or in other words i have a Stock IN sheet and a Stock Out sheet
is there some sort of a formula which links 2 cells, columns, together in a way that, if one cell's number increased the other would decrease automatically ?
say iv set 50 in cell a1, i want to set 10 elsewhere and the 50 in cell a1 will automatically decrease from to 40
here's my excel worksheets i uploaded it,
View 7 Replies
View Related
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
View Related
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
Sep 18, 2006
Simplified:
I have a this years total in A1, last years total in B1, difference +/- in C1
$1000.00___$800.00___+$200.00___+%
$800.00___$1000.00___<$200.00>___<%>
I am trying to get a % of Increase/Decrease (Between A1 and B1) in D1.
Not as a dollar amount but as a %.
I have tried Percentile and PercentRank and some other stuff. No joy.
View 12 Replies
View Related
Oct 16, 2013
I have a table that has labels of departments. Then each of those departments will be tracked per week on a number. I am looking for a formula for conditional formatting that if cell C5 is less than B5 that cell C5 (with the smaller number) fills green. Then depending on the next weeks number it will have no fill or a green fill if the number has decreased in cell D5.
Is there anyway that I can do an array of cells or do I have to do it individually per column?
View 2 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
Sep 28, 2009
how to change the cell colour due to a sum increase or decrease? For example: if the amount is greater than 200, change cell colour to green. If under 200 change cell colour to yellow.
View 2 Replies
View Related
Dec 15, 2008
I have a worksheet where over time I add columns that need to be added in a "Total" cell.
=M3+W3+AG3+AQ3+BA3+BK3+BU3+CE3+CO3+CY3
Above example, the cell being added is 10 columns after the previous one.
Question:
What formula can I use to automatically pull the value from every 10th cell starting with M3?
Preferably every 10th cell till a value I determine... i.e every 10th cell but only for the first 15 occurrences.
(Is this anything to do with the Series command?)
View 9 Replies
View Related
Jun 4, 2008
In the formula =IF(" cell # "","") what does the "","" mean
View 4 Replies
View Related
Jun 3, 2006
I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?
Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............
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
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
Apr 30, 2013
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
View 2 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
Dec 11, 2013
I'm dealing with the following problem. Say I have a set af values:
A B
1 567
2 546
3 567
4 554
5 572
6 543
I can easily determine the average of the values in column B (=AVERAGE(B1:B6)) But I also want to know if the average is increasing or decreasing without making a chart and trendline. Concrete: I want the cell with average (say B7) to turn green when the trendline has a positive growth and red when negative.
View 2 Replies
View Related
Aug 2, 2007
Is there a formula or maybe some VB code that can be used to decrease a cell value if another cell's value is changed. e.g. I have a userform that has a Textbox that will put a 6 digit number in cell J4 & a Combobox that enters a number in cell E4. If the value of J4 is changed from the Textbox, I would like to have cell E4's value decrease by 1. If J4 doesn't change, cell E4 remains unchanged.
View 2 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
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
Mar 27, 2014
I have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.
View 5 Replies
View Related
Oct 22, 2007
I'm just fiddling around with excel at the moment and have a created a cell with a value of 16. It represents the number of days an event has been running for.
I'm not sure how to make it increase by +1 every day without me having to open excel and change the value in the cell.
View 2 Replies
View Related
