Chandoo. org - Complete Archives of Excel tips, Charting Tutorials, Dashboard Templates and More.. Chandoo. org. All articles: Showing latest post on top. D’oh – Visualizing Homer’s favorite sayings in Power BIKeep Calm and Power BI [Breathing Exercise Vizzes]Power BI Play Date is here. Come, join us for a play. VLOOKUP that fat table with ease [3 quick tips]Epic Introduction to Power BI [video]Introducing Power BI Play Date online class [Details inside]Convert unevenly spaced list to table [Data from Hell]Copy Paste Visible Cells only (Two more ways to do it)Copy & paste visible cells only [Excel Trick]Awesome chart to visualize Salary Increases for 3,5. Tutorial]Employee Performance Panel Charts in Power BI with REmployee Performance Panel Charts – Excel vs. R [video]Use File > Info to quickly unprotect multiple worksheets [Quick tips]Extract currency amounts from text – Power Query Tutorial. Joyplot in Excel. How to add a lot of Goal Seeking to a model. Rounding time to nearest minute or quarter hour etc. SUMPRODUCT Vs. Power Query on Mt. ![]() ![]() Kau. Kau. Jo’s first keyboard shortcut. Start your bar charts from zero – Excel geeks screaming at you from mountain top. Selective Sub- totals in Pivot Tables [Quick Tip]Avoid Hiring Boo- boos with Excel – COUNTIFS for the win [video]Relative References in Excel Tables. Modelling Inventory Run Rate & Cash Flows using Excel. There are 5 hidden cells in this workbook – Find them all [Excel Easter Eggs]How many employees are on leave during Easter holidays [Homework]Hide columns one one tab same way as they were in another place [quick tip]Sand Pendulums – Lissajous Patterns in Excel. Reshaping your data easily – Case study [Pivot tables FTW]Figuring out Employee Churn with Power Query [HR Analytics]Kill NULLs – a Simple macro to save time when importing data from SQL Server. Lookup most frequent item [Homework]Use CTRL to make copies of worksheets quickly. Find them and Extract them – VBA Macro. Senturus' comprehensive library of Cognos and Business Intelligence presentations and information. All of them are free and we add new resources regularly. Designing awesome financial metrics dashboard [tutorial]Road Trip Planner Template [Excel Downloads]Get rid of that ugly formatting with two simple tricks. Check for two out of three conditions (Homework)Untrimmable Spaces – Excel Formula. Formula Forensics 0. Reverse Text – A Formulaic Solution. Merry Christmas & Happy New Year 2. Holiday Greeting]An odd lookup problem [Formulas]Sorting to your Pivot table row labels in custom order [quick tip]We Want Your Ideas. Finding the closest school [formula vs. Can you solve this blood pressure problem? IF Formula Homework]How to add a line to column chart? Charting trick]Decorate your TPS reports with spooky spider web chart [Halloween Fun]CP0. So which formulas you should care to learn? Find first & last date of a sale using Pivot tables [quick tip]How many formulas should you learn? Weekend Poll]Finding if a cell has 7 in it… [Pattern matching in Excel]CP0. Yes, I am back” edition (and a bonus Excel tip)Interactive Decision Tree Visualization in Excel [Trump vs. Hillary in Swing States]Currency format Pivot fields with one click [Friday VBA]How to generate all combinations from two separate lists [Pivot Table Trick]A quick tip about data analysis while on bike [Video + Personal]Check if a range has all numbers from 1 to n [Homework]Mapping Spread of Obesity [Chart Critique and Alternatives]Visualizing Financial Metrics – Contest Winners. Visualizing target vs. Biker on a hill chart. Stacked Bar/Column Chart with Indicator Arrows – Advanced. Formula Forensics No. Convert a Roman Numeral to a Number. Sara’s Copy Shop – Break even analysis and what- if modeling in Excel [Videos]Stacked Bar and Indicator Arrow Chart – Tutorial. Microsoft’s experimental projects division has released a toolbar utility for Mac-based Office 365 users that lets you access your OneDrive files, make video calls. Version 2.0 of Microsoft's cross-platform,NET pivot changes everything. Join us as we explore.NET Core 2.0, ASP.NET Core 2.0, and.NET Standard 2.0. Last week Microsoft released a public preview of a new service called Azure Archive Blob Storage, offering customers a lower-cost cloud storage solution for rarely. Tell me about an analysis problem that you couldn’t solve with Excel? Hourly Goals Chart with Conditional Formatting. Visualizing Financial Metrics – 3. Alternatives. PSA: Don’t let auto correct spoil your party. Analyzing 3. 00,0. ![]() Weekend open mic – Share your one hand Excel shortcuts. Excel Links – Getting used to life in Windy Wellington Edition. Find the seals [Excel puzzle]Correlation vs. Causation [Charting Chatter]Add any number of days, months or years to a date with this simple trick. Shortcuts for you, one for every weekday [Awesome August]Introduction to Forecasting in Excel 2. Charts & Visual Analysis]How to get Maps in to Excel charts & dashboards [Master Class]Find out how many times a value is present in a cell [formulas]On / Off conditional formatting with this simple trick. We are moving to New Zealand [personal]How to visualize multiple variables over several years? Contest]Teach coding to your kids with this maze game [VBA]What is the sum of values excluding items on stop list? Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]Generating sequence numbers from cluster values [VLOOKUP to the rescue]Show more of your workbook on screens [quick tip]Fish Eye Effect for highlighting selection – Is it effective? Advanced Charting]Excel Tips, Tricks, Cheats & Hacks – Readers Edition. SUMPRODUCT – Beginner to Advanced [Master Class]How many ‘Friday the 1. Formula fun + challenge]Excel Tips, Tricks, Cheats & Hacks – Readers Edition Prequel. Apply Conditional Formatting using Slicers. Extract the 1. 0 digit number [formula homework]Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non- MVP) Edition. Excel Tips, Tricks, Cheats & Hacks – Excel Ninja Edition. Earth Venus cosmic dance – Animated chart in Excel. Sumerian Voter Problem [IF formula homework]Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition. Figure out slot from given time [quick tip]Advanced Interactive Charts using Excel [Master Class]Introducing Chandoo. Monthly Master Class. Tell all versions of truth [Dashboard Best Practice]CP0. Top 1. 0 Pivot Table Tricks for YOUA slicer that doesn’t slice [Pivot Table Tricks]There are seven pandas hidden in this workbook [Easter Eggs]These icons are so pretty, can I get them in green? How to create animated charts in Power Point [VBA]CP0. Excel Data Validation for Dummies. Unpivot and then pivot for clarity (case study)“How Trump happened” in Excel [visualizations]Autosum many ranges quickly with Multi- select & ALT= [quick tip]Please join 5. Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]Analyzing half a million customer complaints – Regional Trends [Part 2 of 3]Analyzing half a million consumer complaints [Part 1 of 3]Formula Forensics 0. Apportioning Sales by Criteria. Not so wild lookups [video]CP0. Book Review – M is for Data Monkey by Ken & Miguel. February, 2. 01. 6 (Wednesday)Use slicers to create a cool selection mechanism [quick tip]Make 1,2. Excel [formulas]Format charts quickly with chart styles & color themes [quick tip]Transpose this address data [VBA homework]Don’t make your charts heavier than they should be – The weight of the world chart [case study]CP0. VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered. How can I help you in 2. Survey]2. 01. 6 Calendar, daily planner Excel templates [free downloads]Best of Chandoo. People & websites that helped me in 2. Merry Christmas & Happy New Year 2. Holiday Greeting]Generate a snow flake pattern Excel [holiday fun]How many Mondays between two dates? Color changing line chart [tutorial]KPI Contest Winners. CP0. 50: Fifty Excel Tips to make you awesome. Pricing Tier Lookup formula. KPI performance charts & dashboards – 4. CP0. 49: Don’t do data dumps!!! Edit cells & formulas faster [shortcut]CP0. How to create animated charts in Excel? Happy Diwali for all our readers – Animated Flower Pot Cracker in Excel for you…Employee training tracker & calendar – tutorial & download. Can you extract numbers from text – homework. Save a range as text file using VBA [tutorial]Contest: Visualize KPI data. CP0. 47: Best Excel tools for Entrepreneurs. Formula Forensics No. Find the Cell Address for a value (2. D & 3. D Reverse Lookup)Formula Forensics No. Find Which Worksheet a Max or Min Value is located on. CP0. 46: Gantt charts & project planning using Excel. Use NUMBERVALUE() to convert European Number format. Excel is 3. 0!!! – what was your earliest memory of Excel? Weighted Sorting in Excel [video]Unpivot data quickly with Power Query [tutorial]CP0. Introduction to Monte Carlo Simulations in Excel. How countries spend their money – chart alternatives. Show forecast values in a different color with this simple trick [charting]Excel Links – Dashboards book delayed edition. Predict values with regression analysis- [Narrator] Let's talk about a specific exampleusing regression analysis. In this case, we're looking at predictingthe estimated home heating oil useby a particular customer, based on three different factors. A variable that we call the intercept,the temperature outside,and the level of insulation in the customer's house. Let's focus on these bottom three rows in the table,and in particular, focus on the column labeled Coefficients. These coefficients tell us the impacton home heating oil usein a particular month given the temperature outsideand the insulation level of the person's house. Now, let's see how we would use this in a prediction. There's three steps to the prediction,run the regression, which I showed you the outputfrom the previous slide,then we're going to save those coefficients. That was those last three rows of data. For example, this will help us to measure the impactfor each additional inch of insulation in a person's house. And then, finally, we're going to use those coefficientsand our expected value for the future to get a prediction. For example, if we were trying to forecast home heating oiluse for a particular individual, we'd use our coefficients. In this case, for temperature, that's 5. Maybe it's going to be 4. We'd put in 4. 0 where we have Temperature,and we'd put in the level of insulationin the person's house where we have Insulation labeled. We then crank through the math and this puts out the levelof estimated home heating oil that the person will consume. Now, of course, this isn't going to be perfect. There will always be some small error associatedwith a particular prediction. Regressions help us to minimize that error, and moreover,particular points in the regression are going to tell uswhat the expected error will be and whether or notwe have a good prediction. So let's use this concept and take it back towhat we talked about previously while we were discussingthe impact of sales culture on a company's revenue. This regression shows us again the impact on salesfrom a variety of different variables related to a company. The sales culture of the firm, but then a varietyof other baseline characteristics like the total debtfor the firm, the amount of capex they, have etc. Our coefficient of 1. The R squared value,or R- sq, that you see at the top,tells us how effective our prediction is. In particular, those numbers tell us the percentageof the time that we can perfectly predict our salesbased on all these different variables. In this case, for the overall regression,we can perfectly predict sales. That's what that 0. Just less that one third of the time,we can perfectly predict sales given these other factors. Now, even when we can't perfectly predict sales,we will still have only a small error termif we've done a good job of building our regression. The second column here, P greater than t,tells us whether or not we've done a good jobin picking our variables. As long as those numbers are small and close to zero,that indicates that those particular variablesare effective in helping us make predictions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |