Excel Formula Challenge
January 29, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
When you're creating an Excel workbook, do you ever want to hide some of the information temporarily?
In the past, I've built complex questionnaires in Excel, and some of the questions had a follow-up item, based on the first answer. To keep things from looking cluttered, only the first question was visible. The follow-up item was in white font with white fill.
For example, "Do you have dependents?" Select No, and nothing happens. Select Yes, and the next question appears.
For step-by-step video and written instructions, see my Hidden Questions blog post. And yes, the video shows a merged cell, but that's what my client wanted!
See more examples on the Conditional Formatting Examples page on my Contextures website.
Join Mynda Treacy for a free one-hour webinar, and learn how to build interactive dashboards in Excel, with or without Power Query. The live webinars are only available until Thursday, Feb. 7th, so click here to register today.
If you want to learn even more, Mynda offers full dashboard courses too. You can get 20% off if you register by Thursday, February 7th. Click here for the Excel Dashboard Course or the Power BI Course
Last week, someone asked how to create a list of 80 random numbers that added up to 30%. My first thought was to use the Solver add-in, but it couldn't find a solution without using lots of zeros.
My solution is below the screen shot, and I know you're creative, so send me an email, to let me know how you solved it. Click here to get the sample file with the challenge and my solution.
Here's what I did. I’m not a statistician, so these numbers might not be acceptably random:
To create random numbers that total 30%, I used this formula from Stackflow, in B1:B80. It divides the random number (A2), by the column A total (E2), then multiplies by the target (E1).
In cell E3, this formulas gets the B total: =SUM(B1:B80). It wasn't exactly 30%, so I pressed F9 several times, to get as close to 30% as possible. When you're happy with the results, copy B1:B80, and Paste as Values, to save them as numbers.
If you have a copy of my Data Entry Popup kit (DVMSP), there's a new version (6.13). Use your original download link to get the latest version.
Here are a few Excel articles that you might find useful or interesting.
Budgets - Send this link to your friends and relatives, if they ask you to help them set up a personal budget spreadsheet. It has steps for Excel, Google Sheets, and other software. They might also need one of the Squawkfox Debt Reduction workbooks. (Level - Intermediate)
Rivalry - In this old article from The Register, Dave Mandl describes the moment when he realized that Microsoft Excel would knock the market leader, Lotus 1-2-3, out of the top spot. (Level - All)
The article about Lotus 1-2-3 versus Excel made me think of sibling rivalry. Tomorrow is my son's birthday, and his older sister was "delighted" when he arrived, as you can see in the photo below. (She claims it was a hug.) We call this picture, "Don't fight it!", because Jack Bauer said that to his victims in the TV series, 24.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190129ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: January 25, 2019 9:42 AM