calculating formulas between 2 dates

[baz0912]

Hi,

I'm using the following formula:

=SUMPRODUCT(((Progress!$G$2:$G$998<=$B2)*(Progress!$G$2:$G$998>=$A2)*(Progress!$J$2:$J$998="not complete")))

I'm using this formula to count the number of "not complete" there are in column J between 2 dates: B2 = start of week and A2 = end of week. Column G has dates in them.

Instead of counting thr cells with "not complete", in "column I" I want to make formulas to take the average of the numbers by week. So i will get an average amount per week.

Do I use the same sort of formula or should I be using a different one?

Any help would be much appreciated

Thanks

Harry

[EdMac]

Hi Harry,

Are you trying to find the average % of all jobs that are not complete from all jobs? If this is so then you need to divide the formula you are using by

Please Login or Register  to view this content.

If this is not the case could you post back with a better description of what you want to achieve.

[oldchippy]

Hello Harry,

May be something like this to get a weekly average up to TODAY() using SUMPRODUCT

[baz0912]

Thanks-you for your responses. Sorry I didn't explain very well. Please see attached sheet.

In sheet 2 I want a formula to provide an average of the number of days on sheet 1 column B between the 2 dates.

Thanks
Harry

[EdMac]

Try this

Please Login or Register  to view this content.

This error traps if there is no data in the relevant period

And copy down

[baz0912]

Ed,

Thanks very much for this. I see how it works, though I am trying to change the cell references to work with another sheet. See attached. On the Stats sheet I have the formula in column G averaging out the days in column K on the progress sheet between the 2 dates. It's not working for some reason and I think it's the fact that I have text and formulas in there. Also spaces. Is there any way of getting round this?

Thanks
Harry

[daddylonglegs]

Try changing to this formula in G2 copied down

=IF(SUMPRODUCT((Progress!$J$1:$J$500>=A2)*(Progress!$J$1:$J$500<=B2)), SUMPRODUCT((Progress!$J$1:$J$500>=A2)*(Progress!$J$1:$J$500<=B2),Progress!$K$1:$K$500)/ SUMPRODUCT((Progress!$J$1:$J$500>=A2)*(Progress!$J$1:$J$500<=B2)),"No data")

[EdMac]

DLL's formula seems to pull the values through as you want


Regards

[baz0912]

Thanks for your feedback!

Harry