Need to calculate average over a date range using several variables

Good evening Excel-ent people!

Can anyone help with this problem? I have a workbook where you can set a variable date range via a 'From Date' cell and a 'To Date' cell. There are then 3x separate tables within (although these aren't actually formatted as tables within Excel). There are a list of employees and targets for each employee within one table. In another table is a set of positions (job roles) for each employee, including the date of any change in position (i.e. a promotion). There is then a final table containing what should be the targets for each position (however there are several columns as these targets can change across the board on a particular date).

It has been set up that there can be up to 3x position changes per employee, and up to 4x wholesale changes to all Targets per position. There are date fields for both the change in Position (in an adjacent column), and also within the Targets table (above each column).

What I would like is a formula to enter into the Targets column of the first table, which calculates the average target over the specified date range. This would need to look at the date range, consider what the target should be at the start of that range, then any changes and when, and calculate an average over that period.

It seems like this should be perfectly possible, although given there are a number of variables here, I can't wrap my head around this.

I had thought that maybe this might require a number of helper columns, but even that I couldn't figure out. And given the actual data set has up to 8x position changes and 10x wholesale changes, I figure that this would require quite a lot of helper columns (so if that can be avoided great - if not, then fine).

Not sure if it complicates things further; however if this were able to look at week days only (i.e. incorporate the NETWORKDAYS function), that would be great.

The Workbook can be found attached. If anyone has any bright ideas, then I'm all ears!

Dummy Report.xlsx

I have calculated manually what these figures should be for cross-checking purposes. Allowing for week days only, the target that I have calculated over the date range as currently populated (rounded to 2x decimal places) are as follows:

Richie - 2.13
Palmer - 5.53
Marshall - 3.87
Milligan - 3.87

Thanking you in advance!

Skins

One way: Plaese try in B5 and copy down:

Formula:

Please Login or Register  to view this content.

Or the whole column B in one go, please empty all cells in B5:B8 and try in B5 (no copy down needed):

Formula:

Please Login or Register  to view this content.

You are Welcome!

Thanks for the feedback and rep .

Glad to hear that you can adjust the formula to your own wishes.
You learn the most from that. .