SumProduct N/A Error, Quarterly Average, Running Average, From and to Userform

Hi,

The attached spreadsheet records dates for steps in an application process and 5 Key Performance Indicators (KPIs) which record the amount of weekdays (how long) between each step to determine any bottlenecks.

The spreadsheet is a subset of a larger piece of data but this is the bit creating issues.


The objective is to record the 5 KPIs as well as a running average. The 5 KPIs are split in Quarters as this is the reporting format. However the quarters are offset one month so the Financial Year starts in August not the customary July.

1st Quarter - Aug, Sep, Oct
2nd Quarter - Nov, Dec, Jan
3rd Quarter - Feb, Mar, Apr
4th Quarter - May, Jun, Jul


The issue I have is as an application is received not all the dates are filled so the calculation I am using creates an "N/A" error until all 5 dates are completed.

Currently I bypass this on the sheet by placing a 0.0 in the column should any date be empty but then this creates a false KPI reading as it includes the 0.0 in its quarterly calculation and adds 1 to the dividing number count thus lowering the KPI.

Also, I am using VBA to push the data to the sheet, but also Excel formulas to determine a way to work out which quarter the row belongs to so it can be summed using the sumproduct function.

I am sure there is a more elegant solution to capture each row, calculate the quarterly KPI just using VBA

Eventually the This Application, Quarterly and Running Average data from the sheet will appear in the userform either automatically (ideally) or when the calculate button is pressed as each application record is cycled through.


I appreciate any insights on how to achieve the above.

Thank you
Darren

Hi newdoverman,

Thank you for the formula snippets. I could not open the attached file (it came up as corrupted?) I have incorporated the formulas into the worksheet. I have made some changes by using dynamic ranges for the KPI column values G:K as well as column M as the rows are added from the userform input so will continue to grow.

I continue to have the following issues:

1. A way to automate column M so that the formula does not need to be extended when each row is added via the userform. Any way this could be automated from inside the VBA code using a Lookup or VLookup worksheet function using column L or a named range on a separate sheet (I have one hidden as "Formula")?

2. Because the application process occurs over time, not all the dates are entered when adding the data to the worksheet using the ADD button. When leaving one or more blank dates in columns B:F the Quarterly KPI value Column relative to the date in range O:S is cleared (but not the running average figures in columns O7:S7)

Any thoughts?

Thanks
Darren

Thanks FDibbins,

I have tried again on a different computer and it opened fine. I will check newdoverman's file and work off that one.

Hi Newdoverman,

Thank you for the updated file. Unfortunatley, I have not been successful in integrating into my example worksheet. I cannot get my Userform to activate, nor the dates to be placed in the worksheet via the userform. I acknowledge your code works so it is something with my Excel program and/or code. As a result I will provide you with Rep as your solution works. Should I eventually manage to get it integrated I will update this post.

Thanks once again
Darren.