• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation Formula approaches > LRG Projects

Stephan

Member
Data Validation combo box approaches for XL Dashboards?

My new project ex0 ZIP file 1.4MB, 1st tab Dashboard DV boxes Cells S2 & S5 either use manual DV or Defined Name sort approach (see tab CITIES), 2nd tab raw data 8000 rows & 50 Columns. DV alternative may speed up data processing, unless just my laptop! Advise if file doesn't bring your LAPTOP to standstill on selection of Combo Boxes!

Defined Name sort > hugh projects significantly slow CPU & increase file size. ex0
Index Match Countif > hugh projects significantly slow CPU & increase file size. ex1 DV attached 4Col R100 AZ SEC.xls my own developed version of dynamic data validation
Indirect Substitute
> time consuming to create for each category & sub category. ex2 attached DVIndSubOffV.xls
Offset Match Countif > hugh projects significantly slow CPU & increase file size. ex3

Many Dashboards, few use automation, just manually enter or Defined Name Range, but for large projects these just aren't viable, advisory comments or example files would be useful!
 

Attachments

  • DVIndSubOffV.xls
    89 KB · Views: 3
  • DV 4Col R100 AZ SEC.xls
    827 KB · Views: 4
On my Core i5, 16GB ram laptop (multi-threading turned on). Each time selection is made for Police Force/City Authority... takes about 5 to 10 sec for calculation to finish.

Normally, I'd use PivotTables for this type of dashboard. Far more efficient than formulas for crunching data. However, I'm not familiar with how it will behave in Excel 2003.

As well, I'd turn Crime03-16 tab in to flat table instead of cross tab structure. To avoid use of SUMPRODUCT as much as possible. It is one of more resource intensive formula.
 
Hi. SumProduct slows modern Laptop? PivotTable useless in Excel 2003.

Comment: Like trying to find reasons, not to do, just what you'd do.
It is alot of Data... 8000 Rows & 50 Columns. Original Data Table = 2.4MB.
CrimeStatsData.jpg

DASH & FORMULA only adds 0.4MB, which is absolutely nothing. Lets explore...

Revised file 2 types:
New version with VB frontend selection menu: DOWNLOAD 1.1MB
& revised version without VB (attached).
CrimeStatsFrnt.jpg


Data Validation changed to INDIRECT SUBTITUTE reduced file size in half.

Formula Dynamic Tables & Dynamic Charts via Combo Boxes on DASH: Cells S2, S5, S8 & S11.

CrimeStatsDash.jpg


1. Crime03-16 tab into flat table? R u suggesting TRANSPOSE so AXIS are opposite? What's the point in that just to turn it all around!
Excel 2003 maximum columns = 256 on Column IV, and data worksheet is nearly 8000rows, it wouldn't fit, even if there was a valid reason!
Doubt restructuring suggestion sincere, slow processing just so much data for XL2003/LAPTOP to search & sum.

2. Avoid SumProduct? Er the proprietary dataset is figures! Summary Tables are Dynamic Tables for Dynamic Charts:
BIG CHART DATA TABLE: D7672:AT7689. 18ROWS. 44COLUMNS
SML CHART DASH TABLE: B25:T28. 4 ROWS. 18COLUMNS.

For comparitive reasons, Index Match per row would be much more resource intensive and have an even larger file size!

Alternatively edits of SumProduct? If its this simple, why make it more complicated?!
=SUMPRODUCT(($B$3:$B$7665=$B$7672)*($C$3:$C$7665=$C7672)*($D$1:$AT$1=D$7671)*($D$3:$AT$7665))

Doubt different edits of SumProduct would alter processing speed:
=SUMPRODUCT(SUMIF(JAN!$CS$2:$CS$500,Q$21,JAN!$CG$2:$CG$500))
OR CONCATE 'esque' style for criteria greater then 3 or more:
=SUMIF(JAN!$CZ$2:$CZ$340,$AA$1&$AA$21,JAN!$BX$2:$BX$340)

Now that's the Data Visualisation formula approach conversation over!
Now for The Dataset!: UK CRIME STATS 2003-16 per Force > City > Crime.
DASH: Decade+ trends via LRG line chart & Year trends via SML bar chart.

Why only certain Crimes are investigated/recorded, and if crimes like Homicide includes major incidents @ negligent Business, you'd have to ask the Police/HSE about that, but figures aren't Fictional/Fictitious, they're all real from NOS, so you'll notice Craggy Island isn't mentioned at all.
 

Attachments

  • CrimeStatsVert#2003-16noVB.zip
    814.8 KB · Views: 9
Last edited:
Back
Top