Contextures

Contextures News 20200414

Excel Formula Challenge For You

April 14, 2020

Show sheet groups, 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.

Formula Challenge

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.

  • This table has production quantities from the morning shift, over 4 weeks
    • Crew A works mornings on odd-numbered weeks
    • Crew B works mornings on even-numbered weeks
  • Use a formula to calculate the total quantities for odd and even weeks.

You can type the data in a blank workbook, or download my sample file with the challenge data.

quantities for odd and even weeks

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:

  • =SUMPRODUCT((ISODD(--($B$2:$B$11))) *($A$2:$A$11))

And here's the formula in cel E3, to total the even weeks:

  • =SUMPRODUCT((ISEVEN(--($B$2:$B$11))) *($A$2:$A$11))

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.

total quantities for odd and even weeks

On Contextures

Here are a couple of new sample files that I've added to my Contextures website.

UserForms

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.

userform controls

Show/Hide Worksheets

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.

show sheet groups

Excel Articles

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?

Also see: My Excel Products || Previous Issues

Baking Bread

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!

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

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.

contextures newsletter info

 

Related Links

Sumproduct Examples

UserForm Controls Demo

Show Specific Sheets

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: April 27, 2020 12:08 PM