Sum a shifting range

[Sir Babydum GBE]

Hi

I have a tricky conundrum. No, really.

On the attached sheet I work out a "Transfer Percentage" by dividing the number of (telephone) transfers by the number of calls. Easy Peasy.

I also need to work out a 4 week average based on a date the user selects.

So if the user selects "18-Feb" the four week average will be worked out as follows:

The sum of Transfers from 28-Jan to 18-Feb
divided by
The sum of Calls from 28-Jan to 18-Feb

Working that out is easy too - except I need the sum range to be dynamic based on the date the user select in cell D2 on the Trans% sheet.

I think I need an Offset() - but how can I do this please?

Sir BD

[Bob Phillips]

Array formula

=SUM(IF(($D4:$V4>$D$2-28)*($D4:$V4<=D2)*(Calls!$D5:$V5<>""),Transfers!$D5:$V5))/
SUM(IF(($D4:$V4>$D$2-28)*($D4:$V4<=D2)*(Calls!$D5:$V5<>""),Calls!$D5:$V5))

[Sir Babydum GBE]

That's poifect

Thanks XLD

BD