Apr 05 2021 02:12 AM
Hello,
I am working on a roster that contains a number of different shifts that are for different hours.
On the roster, we have different codes so people know what they are working.
For example. A 12 hour night shift is C12, an 8 hour night shift is C8, an 8-hour morning shift is A8 etc.
I would like to add the combined hours in each column. However, as you can guess Excel won't recognise the codes as numbers when I hit the sum button.
I believe I may need to create an index table, but I can't find any easy instructions on how to create one, and then how to use it.
Thanks!!
Device and OS Platform: Windows 10 and Mac BigSur
Excel Version: Microsoft Excel for Mac, 16.47.1
Apr 05 2021 02:52 AM
As far as I can tell, the second total should be 0+12+0+8+0+12+12+8+0+8+8+0=68, not 60.
Let's say that the data for 17/5/21 are in B3:B14, and that the index table is named ShiftHours.
The first total is returned by the following array formula, confirmed with Ctrl+Shift+Enter:
=SUMPRODUCT(ShiftHours[Value]*(TRANSPOSE(B3:B14)=ShiftHours[Code]))
This can be filled or copied to the right.
Apr 05 2021 03:24 AM
@Hans Vogelaar , thank you so much for replying.
Yes, you're right the column should calculate 68 hours. My mistake, how embarrassing!
Can you point me in the right direction on how to create an index table?
(How do I create one, where do I contain, how do I access it etc etc)
Thank you again.
Apr 05 2021 04:23 AM
The index table is the table in your screenshot, the one with Code and Value as headers. You can place it anywhere. When you create the formula, simply point to the Value column and to the Code column (without the header); Excel will automatically enter the correct reference.
Apr 05 2021 04:32 AM
You already have a perfectly acceptable Index Table. In 365 you could use
= SUM( XLOOKUP(Shift, ShiftHours[Code], ShiftHours[Value], 0) )
as and alternative to @Hans Vogelaar's approach. In other versions you could sort the table alphabetically by its first column and use LOOKUP
= SUMPRODUCT( IFERROR(LOOKUP(Shift, ShiftHours),0) )
The TRANSPOSE idea also works fine.
Apr 05 2021 04:40 PM
Hi @Peter Bartholomew ,
Thanks so much for trying to help.
I really feel like a goose because I can't grasp how to make the formulas work that both you and Hans are suggesting. I understand the concept.
When I pasted the formula into one of my columns in the cell next to total hours.
= SUM( XLOOKUP(Shift, ShiftHours[Code], ShiftHours[Value], 0) )
I received the error "There's a problem with formula ... not trying to type a formula? etc etc.
I suspect when I am pasting this formula I'm not linking it to anything, or I am missing a step to make this formula active.
I've attached a copy of the spreadsheet, and just replaced the names with generic ones.
If someone can show me how to put it together, I am very happy to learn how to do the rest.
We have Office 365.
Thank you again,
Apr 06 2021 12:26 AM
Hi. I have implemented both Hans's formula and mine on your newly-provided workbook. The error you obtained was caused by the fact that your reference table retained the default naming 'Table1' whereas Hans had assumed it to be renamed to 'ShiftHours'. Both solutions refer to a single (relative) column of the main data array. Hans used a direct referencing which needs to be re-specified; I used a Name 'shift' that needs to be defined within Name Manager.
I also introduced a new formula to generated the day numbers from the start date. It may be possible to generate all the totals as a single dynamic range using MMULT to sum the columns of data but I have yet to try that.
Apr 06 2021 12:35 AM
Apr 06 2021 12:56 AM
Thanks. I must have attached the download rather than the new version!
Apr 06 2021 02:19 AM
Since you are a 365 user, I have added 3 further solutions. The first 'Arr' uses MMULT to evaluate the daily manning levels for the month as a single calculation. The second 'LET' uses the LET function to provide additional local variable to make the calculation more meaningful. The final one 'LAM' uses functionality that is still on beta release. The LAMBDA function will allow the user to define a formula in terms of dummy variables and later provide the actual variables as parameters, thus allowing the user to define their own functions.
Somewhere along the line, you will reach the point where your reaction is OMG, let me out of here! I still believe there is no harm in knowing that other solution strategies are possible.
Arr
= MMULT(
SIGN(TRANSPOSE(fte)),
XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0)
)
LET
= LET(
rowFilter,SIGN(TRANSPOSE(fte)),
shiftValues,XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0),
MMULT(rowFilter,shiftValues) )
LAM
= LET(
shiftValues,XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0),
FILTEREDTOTAL(shiftValues,SIGN(fte)))
where FILTEREDTOTAL is defined to be
= LAMBDA(Array,filter,
MMULT(TRANSPOSE(filter),Array)
)
Have fun.
Apr 06 2021 02:46 AM
Thanks!