I'd like to apply a curve to a column of student exam grades and have the curved grades displayed in the adjacent column. Here is how I'd like to curve:
I like to make a linear function out of two coordinate pairs.
First Pair (x1,y1) = (Highest Exam Grade, 99) = (H,99)
I have a data set (Set#1) that consists of a positional rank (1-240) and a dollar value ($46-$1) for each positional rank. It's not a straight line set.
DataSet.JPG
What I need to do is take that curve and apply it to another data set (Set#2) (1-240) and assign a dollar value ($46-$1) to each positional rank.
A "corner cutting" way of doing it would be an Index/Match to simply carry over the dollar value from (for example) #45 from my Set#1 to #45 in my Set#2. This could work reasonably well with data sets of the same size, but mine aren't always the same size, so I need to apply the curve itself. I
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
I have a workbook with over a 100 worksheets and need a macro that will apply the following column width to all the worksheets. Col S → 4; Col T → 5; Col U 5.2; Col V → 5; Col W → 4; Col X → 7.5
Hello, below is a sample of my excel sheet. The number of rows keep changing daily and the Month and year column has to be populated automatically as and when there is a new row inserted. I tried the formula =MONTH(A:A) and I paste it on the entire column. it works for rows with data but I also get 104,000 odd blank rows added to my excel because I pasted the formula on the entire column.
way to put a simple formula in a colum that will apply to the entire column, no matter how many rows without having to physically copy it to the number of rows required?
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB: Sub Macro1() ' ' Macro1 Macro ' Range("B10").Select Cells.Find(What:="as % of Revenue").Activate Range("B20").Select ActiveCell.FormulaR1C1 = "=R[-1]C/R10C" Range("B21").Select End Sub
I have a column (A) where there's a date in each cell. Currently it is formatted as category "text". All entries where initially entered as 'yymmdd', but using category "number". Beacause of that all zeros in the year disappeared, so now they are shown as "ymmdd".
Can someone show me/tell me how to convert all these dates from category "text", to category "date", using format "yymmdd"? I have tried again and again and again, but can't make it work. I have attached a sample of the sheet.
I have a spreadsheet and I want to color particular cells in a column with a new color - i.e. any new changes need to be highlighted. I know there's a way to do tracking changes in excel, but it just sticks a little flag almost invisibly in the corner of the cell. I want to be able to bring the spreadsheet back to our administrator and say hey the stuff in red is new.
On a related note - I am working on this massive spreadsheet that is a .csv but I am saving it as an exel spreadsheet - is that ok? I am assuming that if I save it as a csv, it will return to the original formatting just without the colors, filters, etc changes I made - which is fine because I think somehow the .csv file will be uploaded to the system and no further changes need to be made.
I found out the hard way when you have a .csv file and make changes and then save it, you lose all the fun row/column size adjustments, color, etc - but I figure in the meantime I'll work on it as a excel spreadsheet and then return it to it's natural .csv file status.
I work with an excel sheet to produce a list that gets distributed (in hard copy). A problem that I have is sometimes, the list spansmultiple pages, so I have to manually use the page break preview to put the column headings on the first row of each page.
I need to do something to my workbook, and I need to do this task:
When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..
Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..
So I don't need to insert/delete the row and copy the formula manually for each worksheet..
I know that I could simply solve it with grouping the sheet tab..
But I have plenty of data that needed to be inserted and applied with the formula..
I will attach the little example : insert.xlsx
And one more thing, I received this VB code from [URL] ..... for inserting the column:
VB: Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim lngRow As Long Dim ws As Worksheet If Target.Row = 1 Then Cancel = True
[Code] .....
And this code for deleting the column:
VB: Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim lngRow As Long Dim ws As Worksheet If Target.Row = 1 Then Cancel = True
I am having trouble getting some conditional formatting to apply to all cells in a column in a pivot table. Currently, the conditional formatting is only applying to the top level items in the pivot but is not applying to the lower level items. I can see why it is doing this. the range in "Applies to" is only specifying the rows that contain the top level items. I tried to change the range to D10:D647 but, it reverts back to just the top level items. How to get it to apply to everything?
I am looking for a function [or method] to calculate a polynomial curve fit. I can do it on a graph with the existing graph and chart tools, but I need to calculate it myself. Does such software exist somewhere in my Excel 2007 package, or can anyone recommend a freeware program? I am hesitant to download an unknown, un-recommended add-in.
i am getting some data via RS 232 port and plotting it straight to excel, which comes out to be in forms of waves you can say sine waves, now i need to calculate area under the curve, say like if i highlight some area it will automatically calculate the area, or if i select two points on the curve it will calculate the area. please help me out i am really stuck at this part.
Does anyone know of a statistical formula that would create a normal growth curve?
For example. Let's assume that I want to forcast growth from January through December. I know that in January I will have 100 units. In December, I will have 1,000 units. Is there a formula to predict the the units in Feb-Nov (using a normal growth curve)?
I am trying to calculate the equation of a curve which best fits some data. On the x-axis is date and y-axis the readings. The curve is quadratic, so on the chart I am fitting a 2nd order polyomial and displaying the equation. To prove that the equation the trendline gives me is reasonable, I am applying the equation on the x parameter to get the y parameter estimate and looking to see the error between predicted and actual - see attached.
The equation and the output from it are clearly rubbish, even though on the graph the trendline is a reasonable fit - see attached. Has anyone any ideas why? I think it has something to do with using a date on the x-axis but no idea why. Is there a way to correct for this? I also tried using the following I to calculate the coefficients which came up with a reasonable forecast (although linest should only be used for linear data): =LINEST(C3:C15,B3:B15^{1,2,3})
I am working on Project Planning, I need to create the Progress curve for our Project based on Weight Factor every month.
I need to create the Plan and actual curve based on the dates defined in particular activities. Once the activity progress is calculated every month based on cut off date then it will sum product with corresponding WF to get the progress achieved every month. Presently I am calculating the values every month and pasting the values in Chart data every month. I had created the S Curve and attached here.
Now my objective is to automatically calculate the values for the entire cut off date at one time (From Project Start to End). i.e I don't want to do any manual work in the Chart. I will update only the actual Dates in the file, then the curves need to get automatically updated.
1) I don't want to calculate the progress for individual activities, I would like to calculate the overall progress directly.
2) When I change the Cut off date, then the previous values needs to become static.
I'm trying to find a formula that would spread out a value amongst multiple cells along a bell curve. Say I have $x,xxx. I want to spread that amount out in a certain proportion, regardless of how many cells I'm spreading it throughout.
Say I have a $10,000 project. I want to be able to spread it out like a bell curve in the cells I choose. For instance, if it was a five month project it would be distributed as
January - $1,000 February - $2,500 March - $3,000 April - $2,500 May - $1,000
If I added another month it would change to January - $833.33 February - $1,666.66 March - $2,500.00 April - $2,500.00 May - $1,666.66 June - $833.33
I already thought of manually adding percentages to each cell, but the formula needs to be dynamic.
Can we draw tangent to a curve in excel graphs..? If yes if i could have some demo....** this.. I am working on finding rate ** change at an instantaneous point and average rate ** change ie: secant line
I would like to use excel to plot a growth curve. I would like it to plot the points that correlate to my data without connecting them by a line. I would then like to give excel a formula and have it plot that line on the graph.
I'm trying to graph a bell curve chart using the following data
Hotels per state Total
AK 4
NH 5
[Code] ......
As you can see I have states and the number of hotels per state what I need not for statistical purposes but more for "pretyness" is to sort this data in a way that what you get is a somewhat of a bell shape curve graph.
I want to create a bell curve for my team's annual appraisal. There are 12 people in my team. The scale is 1-5, where normally rating range from 2.9 to 4.0.
I've managed to figure out how to normalize my data and create several bell curves. However, these charts do not display the mean, std deviation, or confidence intervals. Any way to add these into my charts?
