If I am trying to calculate the breakeven moment from a project that has an initial cost and a series of quarterly returns. This is easy to do visually but I cannot figure out how to arrive at the breakeven point from a data set such as this.
e.g. 50,000 spent at time 0
5,000 received in q1
10,000 received in q2
10,000 received in q3
10,000 received in q4
10,000 received in q5
10,000 received in q6
In which month will I break even?
way to insert a Line where the (x) line on a chart is breakeven or the instance where it changes from positive to negative. I am doing this now by just drawing a line, however with a chart that is constantly updating the line does not adjust to this.
View 9 Replies View RelatedI have a set of % score values (e.g. 88%, 94%, 82%, 67%)
I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.
E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%
I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%).
Windows 8, Excel 2010
I have XYZ Coordinates for a continuous 3-D line that has numerous segments. I want to input a distance along that line, and have it create the XYZ coordinates at that point. See Image for reference.
Row 2 is my start point - I input the initial coordinates here - this point is the origin of the 3-D line
Column B is where I want the calculated Y value to go for each point
Column C is where I want the calculated X value to go
Column D is where I want the calculated Z Value to go
Column E is the how far along the 3-D line that the (to be calculated) point should be at. (MD1)
Columns G, H, & I are given to me, and I use this data to generate the coordinates in Columns J, K, & L
Column O is the cumulative length of the line at that coordinate. (MD2)
Basically, I had planned on writing a formula to:
Find the coordinates of the point who's MD2 (column O) is before the desired point's MD1 (column E)Find the coordinates of the point who's MD2 (column O) is after the desired point's MD1 (column E)Subtract MD's (column O) to get the length of the segmentFind the distance along that segment that MD1 (column E) fallsUse that distance to traverse along that line to the desired point.
to figure out a breakeven analysis.
Here is the situation. I bought 100 shares of stock at $40 each for total investment of $4000. The stock price is now $26 a share so the investment is down $1,400. I want to figure out the number of additional shares I need to buy at $26 so that when the price goes to $26.01 i turn a profit.
I trade the stock market and am trying to get excel to automatically calculate the required quantity of shares to be purchased in order to achieve a particular breakeven point. Question: How many shares required at a price of 37.30 to achieve a breakeven of 36.24 when I already have 8000 shares at a price of 34.73? I'm looking for the formula to solve this not just the answer.
View 9 Replies View RelatedI'm trying to figure out a function that allows to me to determine the exact month when I will breakeven. For example, my total net income for the 2 quarters of the year is 1050 and my investment is 500. If I have an income in Jan of 300, 300 in Feb, and 200 in March, then the function will spit out Feb as the breakeven month.
Please see attachment. Exact year to see when I breakeven.xlsx‎
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit
Sub PointName()
Dim Ws As Worksheet
Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet
Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy
Sheets.Add
With ActiveSheet
.Paste
.Name = Trim(Cel)
.Range("A1").Select
End With
Next
Ws.Activate
End Sub
I turned it off for a while and when I turned it back on I am getting an error
Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
I want to pay salespeople a bonus when they sell things at a higher Gross Profit. There is a minimum markup. I want to say for every additional point above the floor (F) well pay (B*C). There is also the actual attainment filed (A). For this example lets say the floor (F) is 22% and the actual attainment is 26%.
Illustrations:
.22 = 0(B*C)
.23 = 1(B*C)
.24 = 2(B*C)
A = .26
F= .22
What I want is:
if A >= F THEN for EACH POINT above the floor add 1*(B*C)
Its actually buried in a larger IF(AND function and I need to be able to drag the function down a long column and have it scale with volume sold. But its just this bit of logic I'm hung up on.
Does anyone know if Mappoint 2006 has automation support? I know that 2004 does. I am wanting to use the automation from excel's VBA.
View 6 Replies View RelatedPicture 2 rows and 4 columns.
The top row is just 0, 1, 2, 3
The bottom row is something like: 850, 790, 200, 250
Here's the problem: I want to find the midpoint of the data set in row 2, based on the values in row 1. So, the sum of the data in row 2 in this example is 2090, meaning the midpoint is 1045.
If we were traversing along the top row as a line, we'd find that midpoint somewhere in the 0.3 range... but how do we get that value?
is there any way to use the right function to return any data to the right of the decimal point?
eg 117.22 would be .22
108.1225 would be .1225
102.358 would be .358 etc
1.jpg2.jpg
I am trying to create a database for part stock but with only one entry point that is in image 1 you would select the machine and part from drop down lists and hit add. It would then lookup the corresponding machine and part and subtract 1 from the current stock database.
I’m using this code to run consecutive programs, which works really well, apart from one little glitch.
View 3 Replies View RelatedSee here:
http://www6.uniovi.es/~antonio/uned/...-754hex32.html
This site allows me to enter a 32-bit Hexadecimal Representation and get Decimal Floating-Point result.
For instance, 4224A012 gives me 41.2.
Is there a UDF available to do this inside of Excel?
I am trying to split the points given to our anglers. here is my formula for assigning the points (column I) based on the place (columnH):
=VLOOKUP(H5,'Supporting columns'!A2:B33,2,FALSE). I tried adding +IF(H5=H5:H33,-1) but this affected all other rankings. if 2 anglers tie for a place, the points for that place are awarded -1.
Is there a way to delete all rows down a sheet until Cell A contains ""
or if the criteria "" would work?
i only want to keep the data below that point.
i will have to do it 11 times for different files
I'd like to point a cell to a subtotal on another sheet but that sheet will periodically have the subtotals removed, more data added, then sorted and subtotalled again. Is there a way to have my other cell find the subtotal that matches a certain criterion (in this case, cost center)?
View 9 Replies View Relatedthe rate and inventory management system my hotel uses rounds up to the next .95 currently. Most rates are based upon a 'Rack Rate' in the form of a discount percentage. So, if the Rack Rate is $129.95 and the AAA discount is 20%, the resulting $103.96 rate is rounded up to $104.95. I'm trying to fix my spreadsheet to account for the various rates and discount percentages of several different types of discounts.
I've tried a couple rounding methods:
=ROUNDUP(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .96-.99
=ROUNDDOWN(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .00-.95
=CEILING((B$30*(1-B32)),0.95) which returns some odd bits, sich as .85 and .50
So, is there a way to get Excel to shift the rounding base point from zero to .95? Or some other way to accomplish this?
Would like to do the following for our school,
In cell A1 there is a number, for instance: 12.12345
Based on that number, the 3rd number after the decimal is important.
If the 3rd number after the decimal is “x” then the contents returned in b2 is a text value ( like high school ) .........
I have a column of data and I want to average the figures from a certain point onwards.
I have the starting point value in a cell which is used as the lookup value to find the matching position in the list using the Match(lookup value, array) formula.
How do I then use that formula as the row number in a cell reference for the averageing formula?
eg Average(C(match(B1,C4:C100)):C100) - what is the correct syntax?
I am trying to position a userform to my own coordinates, but I don't see how to do this in any of the form properties. I do realize that there is a startUpPosition property but none of these positions work for me.
View 4 Replies View RelatedIf I check the box labeled catagory name on the intial creation or within chart options on the right click menu each data point gets labeled with the name of the catagory.
I would like to label a significant event at one of these data points does anyone know how to do this? If this isn't a built in feature does anyone have a work around that would look good?
During finalization I have to convert the numbers into millions. But due to rounding factor, the total doesnot match with the displayed fig. how can I sum only upto two digits of the fig? A sample file is attached.
View 9 Replies View RelatedGoing off this excellent thread : [URL] ....
I'm looking to replace the words in
VB : myWords = Array("dog", "cat", "hamster")
with a named range.
Here is what I did. I added the lines:
VB:
Dim myArray As Variant
myArray = Range("colorbold")
And replaced mywords with
VB:
myWords = Array("colorbold")
Something is clearly wrong.
I want to know if it's possible to calculate the probability that a certain range of values, reach to a certain point.....
The range is this (example):
4
3
9
7
14
22
15
20
42
46.....
What I want to know is if this tendency will reach to 1000. Or how many steps more will be need to reach 1000.
Like I said, and don't know if this is in the right place, but if you put this values in a graphic you will see that the tendency is growing. I assume that this tendency will reach to 1000. But will be in the next 10 steps or in the next 30.
I am supposed to make an xy scatter graph like the one drawn of paper. The average point need to have a "cross" around it. So far I can just plot the points. I have added the average point as secondary series. I just need to put the "+ sign or cross around" it.
View 1 Replies View RelatedI need to add a decimal point to a column of numbers. For example, where it says 126 needs to be changed to 1.26, 3035 changed to 30.35, 13593 to 135.93 and so on. Can this be done automatically or with a formula?
View 2 Replies View RelatedHow can i change this code to point to a file which is on my desktop which is in a folder called "cell n1" and the file is called "Cell n1"
View 2 Replies View RelatedThis problem just appeared recently, as if out of the blue, and it seems to affect all of my old work sheets as well as new ones. I am using 2002 version. When I input any number in any cell the program automatically inserts a decimal point. Thus 1 becomes .1, 12 becomes 1.2, 123 becomes 12.3 etc. I have tried formatting the cells to "general" and to to "number" with no change. The thing that really gets me is that old saved worksheets exhibit this behavior as well. I can work around the problem by inputting 1. or 12. which then gives me the entry I am after but I've never needed to do this before.
View 2 Replies View Related