-3

Problem

I have created quite a convoluted formula in Google Sheets that Sheets doesn't seem powerful enough to handle for more than ~80 rows of data, so I wanted to try moving it over to Excel. Seems like Excel doesn't do "arrays in arrays," which I'm doing by putting a MAP inside of a MAP, so I'm wondering if any Excel wizards can take a look at this formula and make it work in Excel.

Background

I work at a company where a case owner owns a case from a specific start date/time to an end date/time. That case owner can own multiple cases at once. I'm trying to figure out the "labor cost" per case, which is simple to do if they were to own one case at a time. For example, if they're paid $20/hour and work a case from 1/1/25 at 2PM to 1/8/25 at 9AM, they'd have worked 40 hours, assuming an 8AM to 5PM workday and not including weekends, meaning the labor cost for that case is 40*$20=$800.

Things get trickier when, for example, they take up a 2nd case while working that first one. For a simple example, say they started working a 2nd case on 1/7/25 at 4PM. That means there were 2 hours in which they were working two cases. So the labor cost equation for the first case would now be (38*$20)+(2*$20)/2=$780. In other words, we're now multiplying 39 by $20, which is (38 hours/1 case) + (2 hours/2 cases). The equation I created finds this multiplier.

Sheets Solution

Below is my equation in the "Multiplier" column for the example table below:

=LET(
createDate,A2:A8,
retainDate,ARRAYFORMULA(B2:B8-1/24),
names,C2:C8,
morningTimes,SEQUENCE(7,1,ROUND(TIME(8,0,0),3),0),
eveningTimes,SEQUENCE(7,1,ROUND(TIME(17,0,0),3),0),
sundays,SEQUENCE(7,1,1,0),
saturdays,SEQUENCE(7,1,7,0),
MAP(createDate,retainDate,names,LAMBDA(create,retain,name,
SUM(MAP(SEQUENCE((retain-create+1/24)*24,1,create,1/24),LAMBDA(t,IFERROR(1/SUMPRODUCT(names=name,ROUND(t,6)>=ARRAYFORMULA(ROUND(createDate,6)),ROUND(t,6)<=ARRAYFORMULA(ROUND(retainDate,6)),morningTimes<=ROUND(MOD(t,1),3),eveningTimes>ROUND(MOD(t,1),3),saturdays<>WEEKDAY(t),sundays<>WEEKDAY(t)),0)))))))
Create Date Retain Date Names Multiplier
1/1/25 13:00 1/6/25 11:00 Bob 12.166667
1/1/25 14:00 1/8/25 9:00 Bob 22.166667
1/2/25 14:00 1/8/25 10:00 Sam 21
1/2/25 15:00 1/6/25 11:00 Jacob 12
1/3/25 15:00 1/7/25 12:00 Bob 6.66667
1/3/25 15:00 1/10/25 12:00 Sam 31
1/3/25 16:00 1/13/25 13:00 Jacob 49
Improve this question
4
  • 3
    I suggest you re-build the logic in Excel for your formula as many functions don't exist or are not the same even when they have the same name. Commented Nov 12 at 19:19
  • 1
    You might need to use Reduce for the outer Map because it does allow nested arrays, but it can only take one parameter so probably have to use Index a lot to get round it. Commented Nov 12 at 22:20
  • 1
    If you work 38 hours case 1 and 2 hours case 2 you still work 40 hours, not 39. Commented Nov 13 at 6:10
  • @P.b for the purposes of finding out how much a case costs our company, we can't say it costs us 40hrs*$20 for case 1 if the case owner also worked a second case during that time. To simplify, if a case owner worked a case ALONE for 3hrs, that case would cost us $60. If they worked another 3hr case during that same time, it's not costing us another $60. The case owner is getting paid $60 for 3hrs of work, regardless of how many cases they have at once. So in this example, both cases would cost us $30. Does that make sense? Commented Nov 13 at 16:27

2 Answers 2

Reset to default
1

Here is a direct translation of your formula into Excel for interest:

=LET( createDate,A2:A8, retainDate,(B2:B8), names,C2:C8, morningTimes,SEQUENCE(7,1,ROUND(TIME(8,0,0),3),0), eveningTimes,SEQUENCE(7,1,ROUND(TIME(17,0,0),3),0), sundays,SEQUENCE(7,1,1,0), saturdays,SEQUENCE(7,1,7,0), days,SEQUENCE(7), REDUCE("Multiplier",days,LAMBDA(a,i, VSTACK(a,SUM(MAP(SEQUENCE((INDEX(retainDate,i)-INDEX(createDate,i))*24,1,INDEX(createDate,i),1/24),LAMBDA(t,IFERROR(1/SUMPRODUCT((names=INDEX(names,i))*(ROUND(t,6)>=(ROUND(createDate,6)))*(ROUND(t,6)<=(ROUND(retainDate-1/24,6)))*(morningTimes<=ROUND(MOD(t,1),3))*(eveningTimes>ROUND(MOD(t,1),3))*(saturdays<>WEEKDAY(t))*(sundays<>WEEKDAY(t))),0))))))))

Changes:

(1) Change outer Map into Reduce

(2) Reduce only has one 'current' parameter, so need to use this as a subscript referring to createDate etc. using Index

(3) Defined 'days' as a sequence from 1 to 7 for use with Reduce.

(4) Sumproduct does not work with logical values - have to replace commas with * throughout.

(5) Moved 1/24 out of retainDate definition - I think there is a rounding issue here that stops it working properly otherwise - haven't been able to pin it down.

(6) Removed all ArrayFormula references - Excel 365 assumes array formulas by default.

Multiplier
12.16667
22.16667
21
12
6.666667
31
49
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is great, thank you Tom. It's very helpful to see how I can use Reduce and VSTACK together to accomplish the same thing. Where I'm at now is that this initial iteration of the formula, both in Sheets and Excel, isn't powerful enough to calculate my whole dataset (which is about 3000 rows). The answer provided by bricks96 is great and is less intensive than my first formula, but it still isn't strong enough in Sheets. I'm now trying to figure out how to take the concept of that formula and make it work in Excel.
1

I still encourage you to keep working in Google Sheets. For one thing, if you transition over to Excel, you might lose some of the support resources. Namely...us. :-)

I took another look at your problem. My goal was to incorporate the hourly times into the formula and optimize the formula by removing as many Lambda Helper Functions as possible. What's wrong with Lambda Helper Functions? They're great functions, but their whole point is to iterate over an array, meaning everything that you define inside the LAMBDA is going to be executed each time. Whereas, if you can perform some of the calculations outside the LHF or replace the LHF with an array formula, you're going to have big savings in performance.

Here's what I came up with:

=INDEX(
  LET(
   hourlyWage,20,
   createDates,A2:A8,
   retainDates,B2:B8,
   names,C2:C8,
   times,
     SEQUENCE(24*(MAX(retainDates)-MIN(createDates)),1,MIN(createDates),1/24),
   schedule,
     IF((WEEKDAY(times)<>1)*(WEEKDAY(times)<>7)*
        (MOD(times,1)>=8/24)*(MOD(times,1)<17/24)*
        (times>=TOROW(createDates))*(times<=TOROW(retainDates)),
       TOROW(names),),
   hourlyCost,IFERROR(BYROW(schedule,LAMBDA(r,hourlyWage/COUNTIFS(r,r))),0),
   projectCosts,MMULT(TRANSPOSE(hourlyCost),WRAPCOLS(1,ROWS(times),1)),
   projectCosts
))

I wasn't able to remove all LHFs, but check it out. See how well it does with 80 rows of data.

Edit: Here is a spreadsheet showing the formula working. I augmented the final output to include the project costs and the schedule.

Labor Cost Formula

Improve this answer

4 Comments

Thank you very much. However, what's the point of the whole formula that goes into the "schedule" variable? It seems like that just stores the list of names in a row instead of a column. Why not just set schedule to TOROW(names)?
Because ultimately, you're going to need to consider the schedule, hour by hour. That's what ends up happening in the final BYROW. The more work that can be done outside of that BYROW is time saved, because we can use array formula to calculate all at once. Plus, I think it's cleaner this way, because you can output the times, schedule, hourly cost, and project costs individually if you want, since they're individual variable, or combine your output like I did.
That logic makes sense, and I like that it lets me choose to show a variety of data. I realized that my confusion is stemming from the way INDEX works with this formula. Does sticking the INDEX in front of the LET essentially force it to evaluate the "times" variable row-by-row when creating the "schedule" variable? If I remove the INDEX from the front and then change the LET to output "schedule" instead of "projectCosts," it just outputs one row of all the names. Why does adding the INDEX back make it work the way it does?
I prefer to use INDEX instead of ARRAY_FORMULA to array-enable my formulas, because for all I can tell, there is no difference between how they both array enable. I just prefer the shorter one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.