Solved

Statistic

Posted on 2014-02-14
20
123 Views
Last Modified: 2014-02-19
Hi Experts,

I have prices in column  A & variables in column C D E F. I want formula for t StatCoefficients.
 I had done via Tab - Data - Data Analysis - Regression - Results in same sheet which  is look like this Summary OutputBut I want only blue highlighted Cell results via excel formula.......any idea?


See attached file.
stat.xlsx
0
Comment
Question by:itjockey
  • 12
  • 8
20 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39858440
Not too familiar with ANOVA but found this if you know what you want maybe you can find your way there.

http://formulas.tutorvista.com/math/anova-formula.html

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39858489
very useful link working on it & on Scoring too.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39858727
just to recheck what am I achieving is right or wrong?
stat.xlsx
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 29

Expert Comment

by:gowflow
ID: 39858991
0
 
LVL 8

Author Comment

by:itjockey
ID: 39866959
Sir gowflow,

May I reframe the new question as I don't found any Formula. I found macro recording is working. so I thought just go ahead with this same question link with same question next step.


Pls Reply


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39866963
yes sure if we are talking macro have no problem.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39866990
Not too much in next step. need to find numbers of days between regression cycle. I had calculated  regression in column J. I have one excel chart in attached WB in that need to find numbers of the days between A low to B low.
Regression
Thanks
Stat-2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39866997
Oooops this is a new issue altogether ? or I am mistaken ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39867003
yes & I already mention in my post.....what you want me to do close this & ask very new question?


Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39867009
Stat 1 & Stat 2 are actually same -  In stat1 I had copy past special Close & ABCD which is in stat 2 "2pi*/365, cos€, sin€,      cos(2e), sin(2e)". stat 1 I thought not required confusing formula for A BCD so I just pasted values.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39867053
For this question - only need to know how do I get numbers of data point or say Trading Days between Point A low To Point Low B in Chart.


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39867124
ok let me analyse this and will revert.
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39867364
ok here it is. I created a button Get Lowest Points with the below sub

Sub GetLowestPoints()
Dim WS As Worksheet
Dim Ch As ChartObject
Dim Pts As Variant
Dim I As Long, J As Long
Dim PrevPoint As Double
Dim MaxRow As Long
Dim bDir As String

Set WS = ActiveSheet
WS.Range("Z:Z").EntireColumn.Delete
WS.Range("Z1") = "Lowest Points"
PrevPoint = 10000000
J = 2
bDir = "Down"

For Each Ch In WS.ChartObjects
    Pts = Ch.Chart.SeriesCollection(1).Values
    
    For I = LBound(Pts) To UBound(Pts)
    
        If Pts(I) > PrevPoint Then
            If bDir = "Down" Then
                '---> Record ONLY the lowest point in Col Z
                If J = 2 Then
                    WS.Range("Z" & J) = PrevPoint
                    J = J + 1
                End If
                
                If J > 2 And Abs(PrevPoint - WS.Range("Z" & J - 1)) < 30 Then
                    WS.Range("Z" & J) = PrevPoint
                    J = J + 1
                End If
                bDir = "Up"
                
            End If
        Else
            If bDir = "Up" Then
                bDir = "Down"
            End If
            
        End If
            
        PrevPoint = Pts(I)
            
    Next I
    
Next Ch

WS.Range("Z:Z").EntireColumn.AutoFit



End Sub

Open in new window


Active macros and try the attached file.
Let me know
gowflow
Stat-2.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39867851
Perfect Macro it will useful  surely in future....& I am not just saying,  i will... but not for this process...as in this process I need numbers of data point (rows) between 2 lows (& only 2 lows - any two lows) as this is regression so lows & high are identical. actually I am finding duration between two lows.

if you wish to understand than read this as this technical part of this.
Purpose of doing this - by finding duration of cycle we can pin point low or high of stock price. we just need to find where we are in cycle & we can predict future high & low. this is very early stag of this there 3 or 4 more cycles.

apology that I cant make you understand perfectly what I want & you invested your time in this Code but I gona use this Code surly.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39867978
Actually if you notice there are 2 lows I picked the lowest as you had indicated in red. but as I read the post it says that it is interesting to capture low and high of each cycle.

anyway anything wrong in the macro please let me know.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39869681
its seems working but need more data next to result column say Point Numbers. results may be some thing else then shown as I gave match formula & it shows this.

This is addition to actual question so if you want me ask different question then I will else
Add High points too with its point numbers.
i.e. High and Low togather with point number
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39869686
Well as this question is already TOTALLY out of focus from the initial post, I prefer to ask a new related question.

Please do not forget to post a link of the new question if you need my help.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 39869691
ok got it give me 1 min


thanks
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39869692
Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39869702
here it is Statistic.


Thanks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question