Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Excel Function Returns Cell Address

By Russ Mulkey,

Do you load .csv files into excel from perfmon (blg files)?  I took Brent Ozar's suggestion to add the min, max, and average functions. Thanks Brent! However, I wanted to know when (time of day) the counter hit the min and max values.  For example, what is the excel cell address of the absolute value of the max function?  In Figure B below the counter "\SQLServer:SQL Statistics\Batch Requests/sec" occurred at 10:30PM. No, it's not a screamer.

The steps I took:

  • Capture perfmon data into a blg file (can also be created in a csv file format)
  • Convert it to a csv file with relog.exe (a post for another day, but a very handy tool)
  • Open the csv file in excel
  • Insert 5 rows at the top of the spreadsheet
  • Label the rows using the text in the heading column below or your own
  • Paste the formula in the cell address into B1, B2, etc...
  • Change the data range in cells B1 and B2 to match your csv file.  My data range was B7:B788. I excluded row 7 because there was no data in the file.

Be careful when copying cell contents and using relative or absolute cell addresses.  e.g. B1 vs $B1 vs B$1 vs $B$1

Sample excel function to return cell address of min and max values:

cell address heading formula result formula
B1 min cell address $B$36 =CELL("address",OFFSET(B6,MATCH(ABS(B3),B8:B788,0)*1,0))
B2 max cell address $B$208 =CELL("address",OFFSET(B6,MATCH(ABS(B4),B8:B788,0)*1,0))
B3 min function 1,089.16 =MIN(B$8:B$788)
B4 max function 115,378.09 =MAX(B$8:B$788)
B5 average function 14,531.68 =AVERAGE(B$8:B$788)
B6 perfmon counter title
B7 first row of data
B788 last row of data

Figure A. Excel layout manual

Excel graphic of perfmon data. Note the data range's don't match. Pretend data range B8:B85496 is really B8:B788 and it's all good again.

You can also get min and max values and time time of day by sorting / filtering. I found this to be too much clicking around. Yet another way to do this is to load it into a MSSQL table. So many ways and so little time. There are some really great authors that I've learned tons from. However, I cut my teeth with SQL Server Central.

Cheers! I will drink to that.

r

Total article views: 5745 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Stairway to PowerPivot and DAX - Level 7: Function / Iterator Function Pairs: The DAX AVERAGE() and AVERAGEX() Functions

Business Intelligence architect, Analysis Services Maestro, and author Bill Pearson explores the DAX...

FORUM

Excel Function Returns Cell Address

Comments posted to this topic are about the item [B]Excel Function Returns Cell Address[/B] I may ju...

FORUM

perfmon

Running perfmon 24x7

FORUM

SQL Functions in Excel?

Can a scalar SQL function be used instead of Excel formulas?

SCRIPT

Get Client IP Address in SQL Server

A function for getting Client IP Address in SQL Server

Tags
blg    
csv    
excel    
perfmon    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones