Excel Halloween Tricks
October 30, 2018
ADDRESS function, Halloween tricks, and more, in this week’s Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
ADDRESS Function
Last week, you saw how to change numeric scores to letters with the LOOKUP function. Thanks to Alex B, who sent a couple of other formulas – see LOOKUP examples 5 & 6, and my explanation of how they work.
This week, we’ll find the highest value in a list of numbers. Then, the ADDRESS function will tell us where that value is located.
First, a MAX function, in cell F3, returns the highest value from the list: =MAX(C3:C8)
The formula in F4 returns the address for that value, based on its row and column numbers:
=ADDRESS(MATCH(F3,C:C,0),3)
- Row: MATCH function looks in column C, to find the value that MAX returned (F3).
- Column: To keep it simple, I typed 3 as the column number. To prevent problems if the list is moved, use the COLUMN function to calculate the number:
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
Go to the ADDRESS page on my website, to see more examples, and you can click here to download the ADDRESS workbook,
Excel Dashboards
From now until November 15th, Mynda Treacy is hosting free webinars on how to build effective Excel Dashboards, using normal Excel features, or with Power Query features. Choose the session of interest to you, or sign up for both. Mynda’s webinars won’t be offered again until next year, so don’t miss out!
Registration is also open for Mynda’s highly-rated Dashboard course, if you need to improve your dashboard skills to the advanced level.
There is an early-bird discount of 20% off, if you register by November 8th. Click here for the Excel Dashboard Course or the Power BI Course
Halloween Tricks
Tomorrow is Halloween, so just for fun, I made an interactive workbook that shows a few Excel tricks you can use also use in serious projects. There are no macros, just formulas, and other built-in Excel features.
- On the main page, click the spin button, to change the eye colour of the skull, and the message in the text box.
- Click the check box, to select a friendly or scary pumpkin picture. The message beside the check box changes too, based on TRUE or FALSE in the linked cell.
- On the Lists sheet, the ADDRESS function in cell C10 shows the address. I moved things around on the main sheet a few times, and that formula kept track of the spin button’s linked cell!
Click here to download the zipped workbook — . To see instructions for a similar file, go to the Excel Christmas tree post on my blog.
Excel Articles
Here are a few Excel articles that you might find useful or interesting.
Microsoft – The Excel team from Microsoft will have another Ask Me Anything (AMA) session on Reddit, on Tuesday, November 6th, at 2 PM Eastern time. Use this link to join in on Nov. 6th. This is your chance to ask all your important Excel questions, or just see what other people ask!
(Level – Int/Adv)
Slicers – What will you do with Excel’s new dynamic array formulas (when you eventually get them)? Chris Webb shows how to create a list of all selected items in a Slicer.
(Level – Int/Adv)
Charts – On Chandoo’s blog, Hui shows his amazing animated chart, which is a spinning globe. Even if you’ll never build anything like this, take a look, just to see the limits you can push Excel to. (Level – All)
Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour
Holiday Crafts
Our granddaughter came to visit, so we bought a pumpkin, and she drew a scary face on it. She might carve it tonight, with adult help, of course, or leave it as is. It’s hard to decide!
She also wanted to do a craft with my hot glue gun, so we made a cute melted snowman ornament too. It has a pipe cleaner nose, googly eyes and beads for his mouth. Happy Halloween!
That’s it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181030ctx.html
I’ll also post any article updates or corrections there.
Debra Dalgleish
dsdalg@ gmail.com
P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.
No comments:
Post a Comment