[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

Statistic

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
Naresh Patel
Asked:
Naresh Patel
  • 12
  • 8
1 Solution
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
very useful link working on it & on Scoring too.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
just to recheck what am I achieving is right or wrong?
stat.xlsx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gowflowCommented:
0
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
yes sure if we are talking macro have no problem.
gowflow
0
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
Oooops this is a new issue altogether ? or I am mistaken ?
gowflow
0
 
Naresh PatelTraderAuthor Commented:
yes & I already mention in my post.....what you want me to do close this & ask very new question?


Thanks
0
 
Naresh PatelTraderAuthor Commented:
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
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
ok let me analyse this and will revert.
gowflow
0
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
ok got it give me 1 min


thanks
0
 
Naresh PatelTraderAuthor Commented:
Thanks
0
 
Naresh PatelTraderAuthor Commented:
here it is Statistic.


Thanks
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now