0

THE DATA:

I'm trying to create a resource allocation file in Excel and have data structured as follows:

Role Person Week 1 Availability
A Sarah 3
A John 3
A Bill 2
A Emily 0.5
A Leah 0.5
A Mike 0.25

THE GOAL:

Let's say, for example, for Week 1 of my project, I need a total of 7 hours of work done. I need to select which employees have the availability to meet this quota. We can see by eye that Sarah, John, Emily, and Leah have 7 hours of combined availability, but I am trying to write a formula that will call these names together.

PREVIOUS IDEA #1: SUM and OFFSET

I first tried to create a complex function that used SUM and OFFSET to create a sequential sum, incrementing by one person at a time until I reached 7 or over 7. The formula would start with Sarah's availability of 3, then add John's availability of 3 (6 total), then add Bill's availability of 2 (8 total), and then stop and tell me I've reached 8 hours. Drawback: This obviously isn't perfect because we know there's an existing combo that gets us to exactly 7, but because I am summing sequentially, the formula doesn't account for this.

PREVIOUS IDEA #2: XLOOKUP and LARGE

Realizing I had positional constraints with my first attempt, I next tried to use the LARGE function in combination with XLOOKUP to create a sum that wasn't necessarily based on physical position, but on rank. The formula would first subtract the largest availability from what was needed (7 - 3, in our example) and if that was > 0, it would call the employee's name with XLOOKUP and then further subtract the 2nd largest availability from what was still needed. Drawback: Continuation of the formula is my issue here. I want it to say "If 7 minus the largest availability is greater than 0, then subtract the 2nd largest availability. If the result is still greater than 0, subtract the 3rd largest availability, etc. If, however, subtracting the 3rd largest availability results in a number less than 0, then subtract the 4th largest availability instead of the 3rd and check if that result is still above 0, etc.

How can I resolve my first two attempts to reach my desired outcome? Or is there a better solution altogether?! Note that once I have logic figured out, I'm looking to implement some macros to automate some of this process.

Thanks!

edited: spelling and grammar

Improve this question

1 Answer 1

Reset to default
2

Here is one way you could try to get those names which have a combined total work hours of 7 :

enter image description here

• Formula used in cell D2

=LET(
     a, C2:C7,
     b, BASE(SEQUENCE(,POWER(2,ROWS(a)),0),2,ROWS(a)),
     c, --MID(b,SEQUENCE(ROWS(a)),1),
     TEXTJOIN(", ",1,IF(FILTER(c,MMULT(TOROW(a),--c)=7),B2:B7,"")))

And the cumulative sum:

enter image description here

• Formula used in cell D2

=LET(
     a, C2:C7,
     b, BASE(SEQUENCE(,POWER(2,ROWS(a)),0),2,ROWS(a)),
     c, --MID(b,SEQUENCE(ROWS(a)),1),
     d, FILTER(c,MMULT(TOROW(a),--c)=7),
     e, IF(d,B2:B7,""),
     HSTACK(e, IF(d, SCAN(0,a*d,LAMBDA(x,y,x+y)),"")))
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

You're a steely-eyed missile man!
This is great! I noticed that it seems to only work really well if there is only one possible combination of names (otherwise names for all possible combos are listed and then it's the same manual effort to look at them and assign accordingly) AND if there is an exact answer (if I needed 7.1 hours instead of 7, the calculation errors out--not sure if there's a way to account for deltas). Thank you so much for the great start though!
For 7.1 hours it worked for me. You need to change 7 to 7.1 use a cell reference I have hardcoded per your context in OP.
Yes, I changed that hardcoded 7 to a cell reference. I'm getting a #CALC! error when I enter 7.1 however.
Not sure why you are getting works on my end. Also with 7.1 it will include Mike
|

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.