Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Statistic

Posted on 2014-02-14
20
Medium Priority
?
127 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:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
20 Comments
 
LVL 31

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:Naresh Patel
ID: 39858489
very useful link working on it & on Scoring too.

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39858727
just to recheck what am I achieving is right or wrong?
stat.xlsx
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 31

Expert Comment

by:gowflow
ID: 39858991
0
 
LVL 8

Author Comment

by:Naresh Patel
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 31

Expert Comment

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

Author Comment

by:Naresh Patel
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 31

Expert Comment

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

Author Comment

by:Naresh Patel
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:Naresh Patel
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:Naresh Patel
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 31

Expert Comment

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

Accepted Solution

by:
gowflow earned 2000 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:Naresh Patel
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 31

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:Naresh Patel
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 31

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:Naresh Patel
ID: 39869691
ok got it give me 1 min


thanks
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39869692
Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39869702
here it is Statistic.


Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

721 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