# 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:

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: 5776 | Views in the last 30 days: 1

Related Articles
ARTICLE

### Stairway to DAX and Power BI - 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?

BLOG

### Excel in T-SQL Part 2 – The Normal Distribution (NORM.DIST) Density Functions

Today’s blog will be the second in a multi-part series on replicating Excel functions in T-SQL, cont...

Tags
 blg csv excel perfmon