Ignoring Data Points For Regression Lines |
Ignoring Data Points For Regression Lines - Excel |
|
Similar Topics
ie. add points to find their concentration on a standard curve.
Thanks.
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the x-intercept. Any help would be greatly appreciated.
E.g.
Manchester United 3 - 0 Arsenal
Prediction: 4 - 1
Points: 1
I've found a formula online that works for most scores; the correct score (e.g. 3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect sco 1
The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0)))))
Could someone help me work out how I can write a formula to add before the above that will check the contents of four cells (the home score, away score, home prediction, away prediction), and if the number in all 4 cells are the same, allocate 4 points? I've tried using averages, but some combinations would void this. I've also used the SUM function, but the same happens, as with MULTIPLYING.
Something like =IF(A3:D3) are equal, 4,0
Below are a few examples of what I mean:
(Result) (Prediction) (Points)
A B C D E
3 - 3 3 - 3 4
2 - 1 3 - 1 1
1 - 0 1 - 0 3
3 - 3 2 - 2 1
Apologies in advance if I haven't explained this clearly enough.
Thanks
Will
Say I had a line graph for 4 years and I wanted it to be a solid line for the first three and a dashed line for the last one, is that possible?
Many thanks for your help
I'm really hoping someone can help me with this...
I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?
Thanks
Jon
All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards...
And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end.
This will be a completely separate formula on the sheet....
Any help?
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I am sure there is a relativley simple answer but it is driving me mad!
Hope you "Guru's" can help
Thanks
Added example spreadsheet to aid assistance.
It is not a border line.
It is not a page break.
If I delete the rows it appears on it remains visible.
It is not an object floating on top of the spreadsheet.
Any ideas?
Thanks.
I want to split the lines into multiple rows.Each line should come in a different row.
I want to do this using macro.Is there is anyway to do this?
There is an option to split the cell into multiple cells on the basis of the delimiter, but there is no option to split them into rows.
where each cell/column begins and ends. However there are a few cells where
the break with the next cell does not show the line.
I have highlighed the cell in question, gone to format/cell/borders and
everything looks fine. Black color + format border shows square with all
sides of square showing.
Any thoughts on how to fix these few random cells.
Thanks
Code:
With OutMail .To = "name@name.com" .CC = "" .BCC = "" .Subject = "Attrition for" & Range("c5") .Body = "" .Attachments.Add wb2.FullName
When it sends it does not show the name in c5.
It just reads "Attrition for"
Should there be a different code?
When a user clicks on a line in the listbox I want to automatically copy the text in the 3rd column of the selected line to the textbox.
If anyone could provide code which would allow me to do this I would be grateful
Thanks
Colin
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.
Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.
What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.
The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.
Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?
Many thanks.
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time re-identifying which series is
which, using the legend to check the colors.
Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?
Thanks!
Keith
Help
I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.
Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.
Thanks,
Dave