Two of the solutions use the new FILTER function. If your version of Excel has Dynamic Array formulas, you won’t need to do that - just press Enter.In older versions of Excel, you’ll need to array-enter the SUM formulas, with Ctrl+Shift+Enter.=SUMPRODUCT(( MOD($B$2:$B$11,2)=1)*$A$2:$A$11) SUM or SUMPRODUCTĪll the solutions used SUM or SUMPRODUCT to calculate the grand total.įor example, to find the total for even weeks: If you divide an odd number by 2 the remainder will be 1įor example, to find the total for odd weeks:.If you divide an even number by 2, the remainder will be zero.The MOD function returns the remainder when you divide the first number (week number) by the second number (2).Many of the solutions used the MOD function, to check if the week numbers were odd or even. You can get the data, and see all of the solutions in the Formula Challenge sample file. Next, here are some of the formulas that newsletter readers sent to me. There are more Sumproduct examples on my Contextures website. The SUMPRODUCT function returns the total of all those multiplications.Those results are multiplied by the Qty amounts.The two minus signs (double unary) convert those T/F values to numbers (-1 or 0).The ISODD and ISEVEN functions return TRUE or FALSE.I put this formula in cell E2, to total the odd weeks: I used the SUMPRODUCT function, combined with ISODD and ISEVEN. Here’s my formula to sum for odd or even weeks. If you found a different solution than the ones shown here, let me know in the comment section. What pros and cons can you see in the other people’s solutions? You can see my solution in the next section, and below that, you’ll see solutions from my weekly Excel newsletter readers. You can type the data in a blank workbook, or download my sample file with the challenge data and all the solutions that are shown below. Your challenge is to create a formula to calculate the total quantities for odd and even weeks The production data doesn’t show the crew name, but we can total the odd or even week data, to get each crew’s total quantity. Crew B works mornings on even-numbered weeks.Crew A works mornings on odd-numbered weeks. This is the morning data from the first 4 weeks of the year.Įven though the crew name is not listed, we can use the week numbers to identify which crew was working. As you can see, the crew name is not in the data. Here’s a simplified version of the production data, which we’ll use for this challenge. They need to compare the production quantities for the 2 work crews. In this example, the person worked in a factory, where there are 2 work crews – Crew A and Crew B. Have you ever needed to add numbers, based on odd and even numbers in another column? See my solution, and let me know how you’d solve the problem. They wanted help with an Excel formula to sum for odd and even weeks. In that instance, the fifty-third week began on December 24, and the fifty-fourth week began on December 31.Here’s an Excel formula challenge for you, based on an email question that someone sent to me. The fifty-second week of 2011 ends (according to WEEKNUM) on December 24, and the fifty-third week begins on December 25 (a Sunday).Īn even more interesting scenario is when the year begins on a Saturday and the year is a leap year. This is fine, until you get to the end of the year. Since WEEKNUM believes that every Sunday starts a new week, the second day of the week is considered in the second week of the year. Now, January 2 for that year falls on a Sunday. As far as WEEKNUM is concerned, this is in the first week of the year. How can this be? Let's use the year 2011 as an example. Thus, it is possible for the above formula to return up to 54 weeks in a year. You should realize that WEEKNUM always considers the first day of any given year to be in the first week of the year. If you prefer your weeks to begin on Mondays, then you can use the value 2 instead. In this instance, A5 contains a date serial number, and the value 1 indicates that WEEKNUM should assume that all weeks start on a Sunday. This function is used, oddly enough, to return the week number represented by a particular date. One of the functions provided in the Analysis ToolPak is WEEKNUM.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |