Excel Formula Challenge For You
April 14, 2020
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Here's a little Excel formula challenge for you. My solution is below the screen shot, and I'm sure you can find a different way to solve the problem.
You can type the data in a blank workbook, or download my sample file with the challenge data.
How did you calculate the totals for odd and even weeks? Let me know if you found a different solution than the one shown below.
Here's my solution -- I used the SUMPRODUCT function, combined with ISODD and ISEVEN.
Here's the formula in cell E2, to total the odd weeks:
And here's the formula in cel E3, to total the even weeks:
NOTE: I tried the formula without the 2 minus signs (double unary) after the ISODD and ISEVEN, but that resulted in a #VALUE! error.
There are more Sumproduct examples on my Contextures website.
Here are a couple of new sample files that I've added to my Contextures website.
See how to use a check box, option buttons and a listbox, on an Excel UserForm. Make selections, and the data is added to cells on the worksheet. Take a look at the code, to see how it works, then add similar features to your own projects. Look for sample file UF0051 - UserForm Controls Demo.
This sample file is an update on my Show Specific Sheets example. Choose a sheet type from a drop down list, and only those sheets (and the Menu sheet) remain visible. The new version has a "Multi" check box -- when that is checked, you can show additional sheet types, without hiding the previously selected type.
There are a few notes about how the Multi option works, and get the sample file n the Download section - it's file number 2 - Multi Selection.
Here are a few Excel-related articles that you might find useful or interesting.
Easter Eggs: Here are 2 "hidden treasure" links for you. 1) Try Chandoo's Excel Easter Egg Hunt. Solve 20 puzzles, to complete the challenge. 2) Have you seen the "Pivot!" Easter egg in Google Search? . (Level-All)
Exercise: In a short video, Frédéric Le Guen shows how you can use Excel to exercise at home (after you move out all the furniture!) (Level-Int)
Logic: The task in this video seemed simple - write the exact instructions for making a peanut butter and jelly sandwich. But, if you do any programming, or design Excel workbooks, you know that things are rarely as simple as you hope! (Level All)
Excel Humour: See what people said about Excel recently. Have you done any Excel magic today?
Are you baking bread while you stay home, like so many other people? We bought flour a few weeks ago, but didn't think about yeast. Now there's none to be found in the stores, but fortunately, we had a half jar at the back of the fridge. Its best before date was October 2019, but so far, it's been working fine. Here's our latest loaf, and it was delicious!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200414ctx.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: April 27, 2020 12:08 PM