piątek, 9 czerwca 2017

Excel: Conditional formatting string numbers...

Why I have never discovered that until today!!!:

[=0]"";rrrr-mm-dd

"If there is 0, then do not put an erroneous date (a result of some bug in Excel insisting there is a 1900-01-00 date)". Is there more? Like coloring the font dependent on conditions met? Supercool!

sobota, 6 maja 2017

Big Data on a laptop, some options.

Data mining on streams.
http://moa.cms.waikato.ac.nz/rmoa-massive-online-data-stream-classifications-with-r-moa/
http://jwijffels.github.io/RMOA/
https://cran.r-project.org/web/packages/stream/stream.pdf

Database light backend.
https://www.monetdb.org/blog/monetdblite-r

Data mining
https://rdrr.io/cran/ffbase/man/bigglm.ffdf.html
https://cran.r-project.org/web/packages/speedglm/speedglm.pdf
https://cran.r-project.org/web/packages/randomForest.ddR/randomForest.ddR.pdf

piątek, 5 maja 2017

Bayes for beginners videos.

How to explain it plain E:
https://www.khanacademy.org/math/statistics-probability/probability-library/conditional-probability-independence/v/calculating-conditional-probability
http://www.watchknowlearn.org/Video.aspx?VideoID=16751&CategoryID=4457

https://www.khanacademy.org/math/ap-statistics/probability-ap/stats-conditional-probability/v/bayes-theorem-visualized
https://www.khanacademy.org/partner-content/wi-phi/wiphi-critical-thinking/wiphi-fundamentals/v/bayes-theorem
https://www.youtube.com/watch?v=Y-V4rfdl3NI
https://brilliant.org/wiki/bayes-theorem/

piątek, 24 marca 2017

Risk matrix in R (interesting readings)



1) Risk matrix examples http://davidmeza1.github.io/2015/12/17/2015-12-17-Creating-a-Risk-Matrix-in-R.html

2) Use ggrepel instead of jitter  https://cran.r-project.org/web/packages/ggrepel/vignettes/ggrepel.html
 for multiple points.

3)  Is there ggrepel for Excel (?) http://stackoverflow.com/questions/30294041/excel-bubble-chart-overlapping-data-label

wtorek, 21 marca 2017

Stats Day2: Covariance

Covariance 
Cov(for sample) = sum[(x - śr(x))*(y-śr(y))]/n-1 ... we are interesting in the sign. Does not provide strength. Is not standardized.

Covariance matrix...diagonal shows variance of each variable, off-diagonal show covariances betw. each variable pair.

Correlation (Pearson, r)
r =Cov(x,y)/[SD(x)* SD(y)]

poniedziałek, 20 marca 2017

Stats Day1

With Spring day 1 I start regular intro statistics study:

My notes from day 1:
Standard Error of Mean (SD of a sample):
SD/ root(n)  - the larger sample size, the error decreases,


Z-Score: (x-mean)/SD
https://en.wikipedia.org/wiki/Standard_score

T-Score:
Conversion Score = z * (NewSD)+NewMean
NewSD for T-Score = 10 and NewMean = 50
T-Score = Z-Score*10+50
More: Essentials of Testing and Assessment: A Practical Guide for Counselors ...Autorzy Edward S. Neukrug,R. Charles Fawcett p 133 (fragment available online via Google)


Coefficient of Variation
CV% = (SD/Mean)*100

ExcelVBA: From recorded macro to reusable function.

Problem: with each change in slicer, pivot chart formatting changes.
 
So I want all my lables vertical (and with each change the damn thing returns to horizontal). My recorded macro did not return anything sensible. Lecture on chart label properties brought me to this solution:

Sub Macro1()
'
' Wersja "Recorder"
'
Dim mySrs As Series

With ActiveSheet.ChartObjects("chValueofOffers").Chart
        

    .SeriesCollection(1).DataLabels.Orientation = xlUpward
    .SeriesCollection(2).DataLabels.Orientation = xlUpward


End With



Next I wanted to iterate through series collection so that I do not need to change macro for more series:

Sub Macro2()
'
' Wersja "Obiektowo pętlowa"

'

Set seriesCol = ActiveSheet.ChartObjects("chValueofOffers").Chart.SeriesCollection
        
For Each mySeries In seriesCol
    mySeries.DataLabels.Orientation = xlUpward
Next

Set seriesCol = ActiveSheet.ChartObjects("chValueofOffersTotal").Chart.SeriesCollection


   
End Sub

 Finally... what if I have several charts on the same sheet that need vertical labels. Why not making it a reusable function with chart name as attribute. Now... with each change in the slicer the labels in the two charts get corrected to vertical.


Private Function DataLabelsVertical(mySheet As String, chtName As String)

 Dim mySeries As Series
 Set seriesCol = Worksheets(mySheet).ChartObjects(chtName).Chart.SeriesCollection

    For Each mySeries In seriesCol
       mySeries.DataLabels.Orientation = xlUpward
    Next

End Function

Private Sub Worksheet_Change(ByVal Target As Range)
   DataLabelsVertical "Value_tenders", "chValueofOffers"
   DataLabelsVertical "Value_tenders", "chValueofOffersTotal"
   DataLabelsVertical "Status", "chStatusValue"
   DataLabelsVertical "Status", "chStatusValueTotal"
End Sub


End Function


 Sources:
http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Loopthrougheachseriesinchartandaltermarkercolors.htm
http://stackoverflow.com/questions/21165581/vba-looping-through-all-series-within-all-charts