UPDATE: Thanksgiving, Nov. 28, 2019. Microsoft has opened a case and is investigating the formula decrease in speed since Oct. 2019.
UPDATE: Nov. 2nd, 2019 - It appears one of Microsoft's Update to either Windows or Office has slowed down the formulas by at least 50%. We are investigating.
This is a Real World CPU Performance Checker (Excel). This is about as real as it gets. This is a real formula businesses uses to calculate unique orders, accounts, whatever by different business lines. It uses as many cores and as much memory as it can. There are 13 unique Business lines within the one company. There are 50,000 Unique accounts randomized into 250,000 rows along with the line of business to which they belong. The formula below calculates all rows and keeps results from row above it, so the lower it gets, the more it has to track and formula time increase as it goes further.
Download Here and read the Readme first: Excel-Benchmark-Tester.zip
I would love to have your results. Use the contact form on the menu if you'd like to send them to me. Please and Thanks!
BEFORE OPENING the Excel-Benchmark-Tester.xlsm Macro Enabled sheet. The sheet takes at least a minute to load. While calculating, it can appear as if it crashed or a white screen. That is the way Excel is, so don't close it, just let it run and the screen will return to normal when it finishes.
Open up Blank Excel Workbook. Go to Formulas tab and at far right are Calculation Options - Choose Manual. Go to File > Options > Formula and set to Manual Calculation and Uncheck Recalculate before save, so you can save results in the sheet without long wait recalculating.
Now open the Excel-Benchmark-Tester.xlsm and the first time you will have to enable content (trust) or Unblock in the File's properties. The macros are for the manual calculate from that Microsoft page because by default Excel AUTO-Calculates. Next Click the 'Calculate Formula Time". When you have three scores, Click in the average score cell and click 'Refresh Average'.
(Dynamic count unique - MULTI-COLUMN)
THE FORMULA COPIED DOWN TO ALL ROWS:
Formula result gets bigger and slower the further down it goes.
It is checking all rows above it as well as current row.
CURRENTLY 250,000 rows but can be increased or decreased by copying or deleting rows.
Column A - 50,000 Unique Accounts
Column B - 13 Unique LOB's
Both can be present multiple times, hence the calculation.
1=TRUE, It hasn't been counted yet (First Count, UNIQUE)
0=FALSE, It's already been counted
Copyright 2019 by Derek D. Moore (MIT License)
Free to use and Commercialize at your own risk. If you publish this, please attribute me, BUT, ATTRIBUTION IS NOT REQUIRED.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.