Contextures News 20190129

Excel Formula Challenge

January 29, 2019

Hide questions, formula challenge, 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.

Hide Worksheet Info

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.

• Cells B4 and E4 have a simple conditional formatting rule: =E2="Yes"
• The conditional format for B4 adds a black font, and E4 adds blue fill and a border.

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.

Interactive Dashboards

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

Random Numbers Challenge

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).

=A2/\$E\$2#\$E\$1

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.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Siblings

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.

Trivia: Canadian actor, Kiefer Sutherland, who played Jack Bauer, has almost as many middle names as Ritchie "Rosebud" Petrie, on the old Dick Van Dyke show

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.

Debra Dalgleish
[email protected] 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.

Last updated: May 24, 2021 8:06 PM