Need help with Multi Table Lookup Formula

[JackChang]

All-

I need help creating a formula to calculate our staffing needs, per location and hour.

On the attached file I have 3 tabs:

1) Sales: Sales by location (1 to 22), by hour, and month
2) Avg Check: Average check amount per location by month (this is a restaurant)
3) Staffing: The number of staff employees we need, based on the sales range. There are 6 tables here, and I need to determine which one to use based the average check amount for each location and month.

I'm having a problem creating a formula to calcuate our staffing needs based on the level of sales. Depending on the average check amount, I need to use one of the six tables.

For example, if I go to the Sales tab, location 1, cell D12, sales are $472. Then I go to the Avg Check tab and look up location 1, their average check amount for Feb is $24, so I go to the Staffing tab, and use the second table “Average check $24-$24.99”. Based on the sales range of $246 - $520”, I need 2 staff because the sales amount is $472, for this location, and this hour. The result I'm trying to return for this example is 2.

Does anyone have any ideas on how I can create a formula to calculate this? The Staffing tab is how I received it, but I can blend the tables together if it works better. Also, I can unpivot the data so the month is in one column if that is easier to create a formula.

Any thoughts or solutions would be appreciated. Thank you

[p45cal]

Need a correction here:

31921

[p45cal]

Also there are values which fit nowhere:
K7 in the Sales sheet is 580.09
When you look that up in the appropriate staffing table, it doesn't fit either 2 or 3 staff:

31922

There needs to be a single threshold, 580 or 581, so that we can ask one of these (taking 580 as an example):
is it >580 or <=580
or
is >=580 or <580
that way there is no way any value falls outside any range (this happens fairly frequently in your data).
Choose in this particular instance whether it should be 580 or 581 and whether that exact chosen figure should be in the larger or smaller staff number, and I'll apply the same logic throughout.

There's also some negative sales to cater for!

Otherwise, I'm getting there:
31924

[JackChang]

Thanks P45cal! Didn't notice the wrong range, I think this was a manual error based on looking at the other tables, so 3 staff start of range would be 556. Negative sales, I would default to 1 staff, the thought being even with negative sales, we still need 1 person working.
Regarding the range, it should probably be <=580 and that should be the smaller staff number.

Thanks again for the callouts and all of your help!

[p45cal]

In the attached, formula starting at cell P2 of the Sales sheet and copied down and across.
In this workbook I have converted your tables into proper Excel tables, named SalesTbl, AvgChk and Staffing.
It's this last Staffing table that is a consolidation of your original 6 mini tables, with some tweaks:

Zeroes have become -1000 to cater for negative sales figures
blank cells which represented no upper limit have been replaced with very large numbers (1E+99)

The Staffing and AvgChk tables are referred to (hard coded) in the formula. The AvgChk table doesn't need to be sorted.
Regarding thresholds, you'll see in the formula instances of <=, >=, < and > and it's these that are important in establishing the correct staffing requirement.
Taking the the specific sales value of 580.09… in cell K7, you'll see that it results in a staff requirement of 3 in cell X7. To demonstrate, change this value to exactly 580 and you should see the staff requirement go down to 2 in cell X7 - hopefully this correctly meets you requirements.

This formula is quite long, so I've converted it to a named lambda formula called StaffReq, you'll see it in use at cell AC2 where I've started making a new results table. It comes with clues as to what belongs where in the formula:
31925

Any question? Come back.

[p45cal]

Update.
I'm sure I attached the workbook yesterday, but I can't see it now.
There was a 'Database error' that came up a few times when I was trying to gain access to vbaexpress.com yesterday
I attach it here.

[JackChang]

Thanks so much p45cal! You are an evil genius.

Just had a chance to open the file, so I am taking a look at it now. Will let you know if I have any questions.

Hope you have a great weekend! Take care!