Excel: Sumproduct / Indirect / Time to calc 2

[Fenrirshowl]

I have an Excel spreadsheet (2003) which is used to monitor post coming into and out of the office, set deadlines, and monitor completion times.

I know the thing should really be in Access but, having to follow orders, it is in Excel.

I am hoping I can improve the calculation speed with some advice from the Masters.

The problem is with the monitoring/reporting side of things. I need to produce two sets of monthly reports, effectively giving figures on all possible variations of the status of each item of post: Outstanding pre deadline, Outstanding post deadline, Completed pre deadline, Completed post deadline, on a month by month basis.

One report is on all post items, another is for a subset of two clients on which we get monitored differently.

The post items is basically a table of data 16 columns long and circa 9000 rows (and counting). The final 3 columns are functions (couple of IF statements and a VLOOKUP - nothing I would deem too serious for Excel)

I have used SUMPRODUCT functions to do the reporting. Unfortunately, due the month by month nature, I cannot (or rather believe I cannot) eliminate the functions for a given month until all post items are complete. [By eliminate, I mean copy and paste values, to maintain the historic data]

An example of the SUMPRODUCT funtion is

=SUMPRODUCT(--(INDIRECT(ColP)=$A23),--INDIRECT(ColH),(INDIRECT(ColB)>=$B23)*(INDIRECT(ColB)<=$D23))

The ColX references are named cells which provide the necessary reference such as Input!X20:X8900 (where the actual cell entry is ="Input!X20:X" & freerow, where freerow is a calculated cell.

The first INDIRECT checks the client, second returns a quantity/weighting value, 3rd and 4th ensure the date the post was received is within the date markers held in B and D columns. This is the most basic of the functions, with other comparisons being included in the SUMPRODUCT fields.e.g.

=SUMPRODUCT(--(INDIRECT(ColP)=$A23),--INDIRECT(ColH),(INDIRECT(ColB)>=$B23)*(INDIRECT(ColB)<=$D23),--(INDIRECT(ColO)<>"Target achieved"),--(INDIRECT(ColK)=""),--(INDIRECT(ColN)="**Outstanding**"))+SUMPRODUCT(--(INDIRECT(ColP)=$A23),--INDIRECT(ColH),(INDIRECT(ColB)>=$B23)*(INDIRECT(ColB)<=$D23),--(INDIRECT(ColO)<>"Target achieved"),--(INDIRECT(ColK)>=$D23+1),--(INDIRECT(ColN)="**Outstanding**"),--(INDIRECT(ColK)>DTPrevReport),--(INDIRECT(ColK)<=DTCurrRep))-O23

There are 14 SUMPRODUCT functions per month, which is what is slowing things down.

Currently, if a piece of post is booked out (by simply entering a date in a given cell) it takes about 25 second for the sheet to calculate and then allow the user to input the second date.

Are there any suggestions to streamline the process?
Please note, I would like to avoid having to turn on Manual Calculation if possible, as it tends to confuse the staff.

I have also been asked to incorporate monitoring of individual recipients (which will be similar to what I already have but changing client to recipient). Obviously this which will slow things down further.

Any suggestions greatly appreciated.

Regards,

Fen

 

[SkipVought]

Hi,

SUMPRODUCT can affect performance. As a matter of course, because I do use SUMPRODUCT as well as user defined functions that access databases, I often have worksheets that take minutes to calculate and so I have MANUAL calculation set and I will F9 to calculate when I need to.

"it tends to confuse the staff" is a sorry, lame excuse for people who don't want to learn how to do their job. Sorry for being so blunt; well maybe I'm not so sorry! Tell them they could use an adding machine, pencil and paper, but they'll be docked for expending excess time and generating excess errors.

So much for railing against people who complain about the tools for their job!
[tt]
=SUMPRODUCT(--(INDIRECT(ColP)=$A23),--INDIRECT(ColH),(INDIRECT(ColB)>=$B23)*(INDIRECT(ColB)<=$D23))
[/tt]
Your formula would be much easier to understand using Named Ranges rather than the INDIRECT.

Beyond that, I'd need to know alot more about the structure of your data and the design of your workbook, to venture further suggestions.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Another suggestion.

If your source data is a table, which it seems that it is, you could use a PivotTable or a Transform Query, rather than SUMPRODUCT formulas.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Fenrirshowl]

Hi Skip

Thank you for the response and for letting me know that some of your spreadsheets take minutes to calculate!

I agree with the "lame excuse" comments, but despite how many times I tell them, pointing out the status bar "Calculate" message as being a major hint, they still never learn. Given it is me that is always asked why it isn't working, I try to avoid F9 calcs as much as possible so I can get on with my own work.

The (simplified) illustrative structure of the workbook is as follows:
Input Sheet
Report Sheet

INPUT SHEET

The Input sheet has a data entry section in cells C3 to C12. Only C3 to C9 are required for a valid entry. There are also 5 calculated entries in C13 to C17 which the user cannot access.

Once the data is input, the user clicks a button to save the information to the data "table". Effectively a copy, transpose bit of VBA to the next free row in the data table held below the input area. I've no problem with this.

Titles of the table are held in row 19, data starts in Row 20. Currently the next free row is Row 8950, but naturally this will update each time an item of post is added to the table.

The columns in order

A: Index number (integer, calculated as the maximum value in the table so far +1)
B: Date received - simply an inputted date
C: Client - Text. Populated by cell validation in C5, copied and pasted as a value.
D: Employee - Text. Similarly populated by cell validation.
E: Service Level Group - Text. Again cell validation input
F: Process - Text. Again....
G: Description - Text
H: Quantity - integer input
I: Comments (optional)- text
J: Pending (optional) - text
K: Date Completed / issued - input date field
L: Turnaround time - function in C13, pasted as a value as the entry will not change
M: Target date - function in C14, pasted as a value
N: Status - function from C15, pasted as function. If statement with results "outstanding" or "completed"
O: Target Achieved - function from C16. If statemement based on target date and completion date to advise whether it has gone out in target or not or whether it is outstanding in or out of target.
P: Client Group - function from C17. A Vlookup function to some background data, pasted as text.

I have a CountA function performed on Column A to generate the value of the next free row (as mentioned, currently 8950). This is in a named range of one cell called "FreeRow".

Each of the values "ColX" is a named range with the function "="Input!X20:X"&FreeRow [substitute column letters for the X as appropriate].

In effect, I was looking to generate a named range reference using the automatically updating ColX named ranges and the INDIRECT function.

Question: Is there a way to achieve the same using the Insert/Names/Define rather than use the INDIRECT function as I have done?

REPORT SHEET

This is the home of the sumproduct functions. Each individual line has a few parameters:
Either an Employee or Client name
Period start and Period end dates.
E.G. in the function

=SUMPRODUCT(--(INDIRECT(ColP)=$A23),--INDIRECT(ColH),(INDIRECT(ColB)>=$B23)*(INDIRECT(ColB)<=$D23))

A23 is the Client
B23 is the period start
D23 is the period end

I hope this explains the set up.

Regarding your second post:
I have never used a Transform Query, so I will look into the various helpfiles to get some understanding. Thanks for the tip!
I will also attempt a pivot table. I KNOW these are quick and easy, but I have a total mental block and can never get them to work. It is my achilles heel on Excel. I always achieve similar results by doing lots of data manipulation, as (for me) it is quicker than trying to get the damned pivot table to work. [Please feel free to ridicule me at your convenience.]

 

[SkipVought]

No ridicule from me. Just encouragement to expand your horizons.

Please post several rows of sample INPUT data and what you expect to see on the REPORT sheet.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Fenrirshowl]

Hi Skip

Here's a fair chunk of "sample" data for part of January 2009.

Including only the columns that get used in the report side of things, we have :

Date Received Quanitity Date Completed Status Target Achieved ClientGroup
09/01/2009 1 09/01/2009 Completed Target Missed Set5
02/01/2009 1 29/01/2009 Completed Target Achieved Set5
05/01/2009 1 29/01/2009 Completed Target Achieved Set5
05/01/2009 1 29/01/2009 Completed Target Achieved Set5
05/01/2009 1 07/01/2009 Completed Target Achieved Set5
05/01/2009 1 29/01/2009 Completed Target Achieved Set5
05/01/2009 1 06/01/2009 Completed Target Achieved Set5
05/01/2009 1 06/01/2009 Completed Target Achieved Set5
05/01/2009 1 07/01/2009 Completed Target Achieved Set4
05/01/2009 1 19/01/2009 Completed Target Achieved Set4
05/01/2009 3 **Outstanding** **Target Missed & O/S** Set2
05/01/2009 1 03/03/2009 Completed Target Missed Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 29/01/2009 Completed Target Achieved Set5
06/01/2009 1 07/01/2009 Completed Target Achieved Set6
07/01/2009 1 07/01/2009 Completed Target Achieved Set2
07/01/2009 1 **Outstanding** **Target Missed & O/S** Set5
07/01/2009 1 29/01/2009 Completed Target Achieved Set5
07/01/2009 1 07/01/2009 Completed Target Achieved Set5
07/01/2009 1 29/01/2009 Completed Target Achieved Set5
07/01/2009 1 29/01/2009 Completed Target Achieved Set5


Results page
(Apologies on the layout - copied and pasted Excel values so hopefully will fall back into line if you copy back to Excel)

ClientGroup Period Start Period End Post received (ref table col H) Completed Outstanding Within Target Completed but Target Missed Target Missed + Outstanding Check total
Set1 01/01/2009 to 31/01/2009 0 0 0 0 0 0
Set2 01/01/2009 to 31/01/2009 4 1 0 0 1 0
Set3 01/01/2009 to 31/01/2009 0 0 0 0 0 0
Set4 01/01/2009 to 31/01/2009 2 2 0 0 0 0
Set5 01/01/2009 to 31/01/2009 22 21 0 2 1 0
Set6 01/01/2009 to 31/01/2009 1 1 0 0 0 0

Essentially the report is the above repeated for all months. It lists client group, calculates for given month, then lists all client groups again, next month etc. Functions can be overwritten for historic months when all post received has been addressed, but not until then.

BTW. I nearly got the pivot table to work (for some of what I need anyway) presumably by accident. I put the Date Received and Client Group in the Row Header, Status and Target Achieved in the Column Header and Quantity in the table data). However, I couldn't group the report into given months - it listed each date and the client group who had post received in turn, then the next date. If you can explain the "fix" for this it would be appreciated. I read the help files on Pivot Tables and, for once, they may as well be in Aramaic.

Thanks for the help.

 

[Fenrirshowl]

Forget the last bit of last post - now found the Grouping and got it to work (had a blank in there which was messing it up).

 

[SkipVought]

Here's a PivotTable I generated from your data. I could not understand your REPORT
[tt]
Status Completed

Sum of Quanitity Years Date Received
2009
ClientGroup Jan
Set2 1
Set4 2
Set5 21
Set6 1
Grand Total 25
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

this may be closer to your report
[tt]
Sum of Quanitity Status Target Achieved
**Outstanding** Completed
ClientGroup Years Date Received **Target Missed & O/S** Target Achieved Target Missed
Set2 2009 Jan 1
May 3
Set4 2009 Jan 2
Set5 2009 Jan 19 2
Jul 1
Set6 2009 Jan 1
Grand Total 4 23 2
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

it tends to confuse the staff" is a sorry, lame excuse for people who don't want to learn how to do their job"

One of the issues with setting calculation to manual is that it can affect other workbooks.
For those less sophisticated users: I suggest using VBA events to set to manual calculation as you open the workbook, to set to automatic as you close the workbook... and use buttons on the worksheet to calculate the workbook. ( Forum707 )


Gavin

 

[Fenrirshowl]

Skip

Your last post is pretty much there. I am happy to say that the pivot table I was able to create is very similar so I am pretty happy with myself. I admit it took me a little while to get rid of subtotals across the columns, but I finally stumbled onto the correct button to get rid of them.

The order of the fields in the row header needs to be year / months / clientgroup to get the right format.

Thank you very much for your time.

Gavin

I think I will follow your advice re opening and closing the workbook. The buttons I have in place already include a Calculate command to ensure everything gets stored correctly. However there is one calculated cell that needs to be updated as the inputs are entered. Obviously this will not work if the calculation setting is on manual so I will use a Worksheet_Change event with Intersect to trigger this calculation.

Many thanks for the advice guys.

 

[Fenrirshowl]

Actually - no need for Intersect!

 

[Fenrirshowl]

OK - nearly there.

I have the pivot table almost as needed. In the columns I have Status and Target Achieved data items, in rows I have Year, Month, ClientGroup.

I have subtotals per month under each column heading. All nice and helpful.

Is there any way to include a second subtotal row of percentages equal to the subtotal divided by the Grand Total of the subtotals?
i.e. if for a month the only subtotals are
Completed Target Achieved = 160
Completed Target Missed = 28
(meaning no outstanding post!)
the percentages would be 85.1% (160/188) and 14.9% (28/188).

Many thanks

 

[Gavona]

If I understand you correctly:
You can add a data field a second time.
Then in Field Settings, Options "show data as" "% of column"
This does not give you a second subtital row but does give you the numbers that I think you want in an extra column.

Gavin

 

[Fenrirshowl]

Gavin

Thank you for your wise words - I think my description may have not been all it could be as I needed "% of row" instead, but I now have what I need. Brilliant.

I will start trying to use pivot tables again after this lot.

Star to both for giving a pivot table incompetent hope that I may yet get to master this void in my skillset.

Cheers

Fen

 

[Gavona]

Thanks Fen,

there is one calculated cell that needs to be updated as the inputs are entered. Obviously this will not work if the calculation setting is on manual so I will use a Worksheet_Change event with Intersect to trigger this calculation.

I assume you know how to calculate just that cell - range(..).calculate


Gavin

 

[Fenrirshowl]

Certainly do - my VBA skills are pretty good in Excel. All the VBA bits of the spreadsheet work a-okay.