project yellow bringing data types and functional ......feb 05, 2020 · functional programming to...
TRANSCRIPT
![Page 1: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/1.jpg)
Virtual Master Class – Andy GordonSenior Principal Research ManagerMicrosoft Research
London Tech Centre – JPMorgan ChaseApril 28, 2020
Project Yellow – Bringing Data Types and Functional Programming to Excel
![Page 2: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/2.jpg)
![Page 3: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/3.jpg)
![Page 4: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/4.jpg)
Transforming the world
through deep research
![Page 5: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/5.jpg)
![Page 6: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/6.jpg)
Excel as a Programming Language
A long-term partnership between Excel and MSR Cambridge
Presented by Andy Gordon, MSR Cambridge – [email protected]
Project Yellow
![Page 7: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/7.jpg)
Aim
![Page 8: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/8.jpg)
So far, we’ve taken Excel beyond text and numbers
![Page 9: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/9.jpg)
So far, we’ve taken Excel beyond text and numbers
![Page 10: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/10.jpg)
![Page 11: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/11.jpg)
![Page 12: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/12.jpg)
What if we offered a code-centric view of the grid?
Calculation View
![Page 13: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/13.jpg)
![Page 14: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/14.jpg)
![Page 15: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/15.jpg)
It’s easy to name cells and use names in formulae.This makes formulae more readable.
![Page 16: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/16.jpg)
![Page 17: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/17.jpg)
![Page 18: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/18.jpg)
![Page 19: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/19.jpg)
![Page 20: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/20.jpg)
Grid-aware search
![Page 21: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/21.jpg)
Studying calculation view
Authoring & debugging
• 20 participants, variety of backgrounds & expertise
• Improvement in task time and cognitive load
Comprehension
• 14 participants, spreadsheet comprehension test
• Improvement in comprehension and cognitive load in half the participants
Longitudinal deployment
• 7 MVPs, using CV for day to day work, ongoing reporting back
• Many uses, most positive feedback around auditing and comprehension
![Page 22: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/22.jpg)
MVP feedback
• Solves the problem that formula view is aimed at• P6: “[The current show formulas feature] is useless to me. […] One of the most particular things
that accountants do is get column size exactly right. And I need to see what the numbers are. I’m going to say words I’ve never said before. I like the fact that [Calc View] is in a task pane. That makes me feel dirty inside. But I love that I don’t have to manipulate the sheet to flip it into formula view. Don’t have to flip the grid. You guys are absolutely on the right track.”
• Very helpful for auditing and debugging• P17: “Calc View has already been extremely useful to me in identifying spurious formulae, no
longer needed, and the situation where not all formulae in a row had been amended to the newer […] version. Calc View is already a winner!!”
• P9: “Just plain text search is really good.”
• P6: “Cool to have name, location and formula in one place. Formula evaluation tool is kind of weak. Calc View is a logical place to enable stepping from one thing to another. Maybe don’t want to make calc view as rich as formula bar. Maybe just need a quick way to get to the formula bar.”
![Page 23: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/23.jpg)
Calculation View helps people author, debug, and comprehend their spreadsheets.
• Results from over a year of testing, across three studies, with ~40 total participants from a diverse range of backgrounds.
• A powerful successor to ‘show formulas’.
![Page 24: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/24.jpg)
Generalising Spreadsheet Functions to Variable-Size Input Arrays
ElasticSheet-Defined Functions
![Page 25: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/25.jpg)
Sheet defined functions
1: define function
2: use like any other =AVERAGE(B3:B7)
Peyton Jones, S.L., Blackwell, A.F., Burnett, M.M.:A user-centred approach to functions in Excel. ICFP 165–176 (2003)
![Page 26: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/26.jpg)
Elastic SDFs
![Page 27: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/27.jpg)
Elastic SDFs
Problem:
• =AVERAGE( X5:X7) too small!
• =AVERAGE( G2:G200 ) too big!
![Page 28: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/28.jpg)
Elastic SDFs
Solution:
• Somehow generalize whatthe user wrote
function AVERAGE<>( B3:B{3+} ) returnsE5 {
E3 = COUNT( B3:B{3+} )
E4 = SUM( B3:B{3+} )
E5 = E4/E3
}
![Page 29: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/29.jpg)
Elastic SDFs
Elastic SDF can be called with any 0
=AVERAGE(G2:G200 )
• Figure out = 198 from the arguments
• Instantiate the SDF sheet with = 198
• Calc it
• Return the result
• Discard the instantiated sheet
function AVERAGE<>( B3:B{3+} ) returnsE5 {
E3 = COUNT( B3:B{3+} )
E4 = SUM( B3:B{3+} )
E5 = E4/E3
}
![Page 30: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/30.jpg)
![Page 31: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/31.jpg)
User’s eye view
Main point:
![Page 32: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/32.jpg)
The magic
![Page 33: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/33.jpg)
Findings from user study (N=20, 7 female)
![Page 34: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/34.jpg)
Elastic Sheet-Defined Functions
Automatic generalization of map/reduce programs from example
Theorem: Elasticization yields most general generalization
User study: Easier for end-users than map/reduce programs
aka.ms/CalcIntel
![Page 35: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/35.jpg)
Aim
![Page 36: Project Yellow Bringing Data Types and Functional ......Feb 05, 2020 · Functional Programming to Excel. Transforming the world through deep research. Excel as a Programming Language](https://reader035.vdocuments.us/reader035/viewer/2022071606/6144051e6cc38f259c25e812/html5/thumbnails/36.jpg)
aka.ms/CalcIntel
Dynamic Arrays and New Functions in Excel! - Office Insider Blog
Video tour of Microsoft Research Cambridge
( gif2xlsx: Convert GIFs to XLSX format )
Yellow Related Links