Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Key performance indicators Results

Would anyone have a recommendation on how to build a tracking system for key performance indicators in Excel? I need something that I can give to managers that would be easy to use and easy to calculate performance. Is there a reliable Excel product that I could buy?

I've designed a Key performance indicator spread sheet updated by staff at the end of each day. To enable sharing (so they don't have to cue up to enter the info.) I've given each staff member a separate worksheet with in the book. (it also helps with data interogation later).

It has a series of Userforms that all feed back to 'main' userform. one of the values is the staff number.

What I'd like to do initially is to be able to use the staff number - value (from userform combobox1002) as the worksheet identifier, i.e. save the userform info to THAT worksheet.

At the moment I'm using the following method to transfer the userfom values. (I'm hoping it will only be an additional line or three added to it or an ammendment to the bold lines).


	VB:
	
[B]ActiveWorkbook.Sheets("data").Activate [/B] 'sets the "data" worksheets as active
[B]NextRow = Worksheets("data").Range("A" & Rows.Count).End(xlUp).Row + 1[/B] 'finds the next empty row
 
With Worksheets("data").Range("A" & NextRow) 'inserts the data from the user form
     
    .Value = TextBox1.Value 
    .Offset(, 1) = TextBox2.Value 
    .Offset(, 2) = TextBox3.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

can any one help??

Morning,

We use a report that needs sorting by YTD (as per header in F4) by KPI '1' in desc order....but keeping all other KPIs with that customer.

Now the challenge with the sheet is that it has merged cells, that 'block' the key Performance Indicators together for each Customer.

To make life slightly more visual, I have attached a cut down version.

Any help appreciated.

The result should show Customer 'S' at the top.

Cheers,

I've looked around and this site has some great information. Finally ran across a question I couldn't find an answer to, so I decided to post. Hopefully this topic has not been addressed before.

I'm creating a fairly complex chart - a spider graph "dashboard" report that
addresses 4 major areas of an organization's performance - finance, quality, efficiency and customer satisfaction. Each category has 4-7 data elements, with a total of 22 data elements on the chart. There will be 2 sections of the report, a data value chart showing YTD, LYTD and Goal values for the data elements and the graph. I envision this as a relatively easy way for management or board to get a snapshot of comparative key performance indicators. So far so good - input form, calculations, data chart and graph are all created and working properly, now it's a matter of presentation.

Each major category is formatted to a different color on the data report - Financial = green, Quality = Red, Efficiency = Blue and Customer Satisfaction = Black. I would like to carry this formatting over to the graph so that the category labels are 4 different colors. I can change the format of the category labels, but I only see how to change them to one particular color, and I'd like 4 different colors.

Any suggestions would be greatly appreciated.

I created a rather complex file which includes several macro's and named ranges. The current input has approx 4000 line items, which I expanded from 2000 this morning. Everything worked fine until I closed then re-opened the file. Now I get the error message "Excel found unreadable content iin ....." Do you want to recover the contents of this workbook? If I click 'yes', the file opens, but all of the formating is gone. I found a reference on microsoft's website, which relates to the "Current Time Member" and Key Performance Indicators within pivot tables. This file doesn't have any pivot tables, and I can[t figure out houw to remove the expressions from all Current Time Member properties. It may be related, I was getting an error message 'Too many formats' before I saved the file. Thx in advance for any advice.
jim

Hello,

I'm Scott Gordon with Webalo. We are releasing a new service, the Webalo
Mobile

Dashboard, and would like to invite you to be a part of our beta program.

The Webalo Mobile Dashboard allows you to easily deliver business
intelligence reports

including excel spreadsheets with data such as key performance indicators,
scorecards

and dashboards, to mobile devices - including BlackBerry, PocketPC, Treo
(700w windows

os), and smart phones.

We're hosting short online WebEx demos every Tuesday and Thursday. You can
sign up at

http://md.webalo.com . We're very excited to hear your thoughts and
feedback on our new

technology. After the demo you'll be given your own trial Mobile Dashboard
account.

You can contact me at

Using Excel 2007, I have a rather complex file which contains many named
references macros and calculations. This morning, I expanded the ranges
within the name manager to accept 4000 rows of input that I paste into one of
the worksheets. Everything was working perfectly, until I saved, closed then
re-opened the file. Now, I get the error message "Excel found unreadable
content". Thereafter I am forced to either close or accept changes which
wipe out all of my formatting on every sheet (20+). I found a reference
elsewhere related to the "Current Time Member" and Key Performance
Indicators, when using a pivot table. This file does not have a pivot table,
however. I'm not certain it's related, but I was also getting an error
message about too many formats as I pasted 3000+ rows and roughly 20 columns
(70,000 + cells). Any thoughts on this?
Thanks in advance

I've created a table similar to a mileage chart to score different shop
performance figures

Eg.

*Score*, *Revenue, Sales per opening hr
R, SPOH*,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each
with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on
each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is
greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is
greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I
can't figure out how i'd use that to return a grade A to F?

--
loscherland
------------------------------------------------------------------------
loscherland's Profile: http://www.excelforum.com/member.php...fo&userid=6709
View this thread: http://www.excelforum.com/showthread...hreadid=533663

I'm creating a fairly complex chart - a spider graph "dashboard" report that
addresses 4 major areas of an organization's performance - finance, quality,
efficiency and customer satisfaction. Each category has 4-7 data elements,
so that there are a total of 22 data elements on the chart. There will be 2
sections of the report, a chart showing YTD, LYTD and Goal values for the
data elements and the graph itself. I envision this as a relatively easy way
for management or board to get a snapshot of comparative key performance
indicators. So far so good - input form, calculations, data chart and graph
are all created and working properly, now it's a matter of presentation.

Each major category is formatted to a different color on the data report -
Financial = green, Quality = Red, Efficiency = Blue and Customer Satisfaction
= Black. I would like to carry this formatting over to the graph so that the
category labels are 4 different colors. I can change the format of the
category labels, but I only see how to change them to one particular color,
and I'd like 4 different colors.

Any suggestions would be greatly appreciated.

I've created a table similar to a mileage chart to score different shop performance figures

Eg.

Score, Revenue, Sales per opening hr
R, SPOH,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I can't figure out how i'd use that to return a grade A to F?

Before I ask my question I would like to thank all of the MVPs and everyone else who takes the time to give assistance to everyone who uses these forums. I read a lot of the questions and reaponses and they have helped me find solutions over the last few years.

My Environment:
I am on Windows 2000 NT operating system on a network. We currently use Office 2000 for most Office applications, except Access. For some reason our Cost Accounting uses Access 95 as the interface to an AS400 to generate daily cost reports for our facility. Unfortunately only Accounting has permission to use this system and the Access interface.

First Question:
The Access program generates reports that are sent out as RTF files in Word. I wish there were an easy way to pull the data from the RTF file to Excel, but I am not aware of any. Each day, I copy the RTF report and past it into a worksheet in an Excel Workbook. I have one workbook for each month, and each workbook has a worksheet for each day of the month. This works well as each day's cost report uses the same format. Is there an easier way to do this?

Second Question:
I keep a lot of Key Performance Indicators (KPI) and frequently link to these monthly cost report workbooks. My KPI worksheets are arranged with all of the data in a row with the date in column A. I would like to be able to select the day tab in the cost report workbook that corresponds to the day of the month in column A. At present I have each cell individually linked to a specific location in a specific sheet.

As I set up for our new Fiscal Year, I would like to be able to copy a cell down in my KPI sheet so that it will look at column A, pull the day of the month (Day function) and pick the specific cell from the worksheet that corresponds to the returned day. My daily sheets are numbered 1 through the last day of each month.

Hi,

First of I am new to the forum but have done as many have I suppose and used the invaluable information posted on here for a long time to help myself fix the problems I have not been able to overcome with my limited knowledge of Excel.

The current problem

Creating a Key Performance Indicators spreadsheet to measure various information for the account.

I have a column - Equipment Type (holds various multiple examples of types of equipment , Text)
2nd column - Time delayed (calculated from other fields hh:mm format)

I want to calculate for example the number of times the equipment type Scaffold was delayed less than an hour, an hour to two hours and finally over 2 hours.

The attached spread sheet (rough draft) I have used sumproduct for some of my calculations but am struggling with this as trying to use an identifier for more or less than is just throwing me out.

=SUMPRODUCT(--($F$16:$F$167="Powered Access"),--($L$16:$L$167="N"))

I use the line above to count how many types of equired powered access were delivered late the column L holds the Y/N for delivered late.

And i tried the following and about one hundred deviants of it to calculate my initial request above

=SUMPRODUCT(--($F$16:$F$167="Powered Access"),--($N$16:$N$167<"01:00"))

Column F holds the list of equipment types and Column N holds the delayed time.

This second array above does not work (obviously that is why I am here now).

Any help would be greatly appreciated as my next query once this one is resolved gets more complicateed

Regards

Andy.

Hi Guys and Girls,

I first want to say I'm happy to be here at this nice active forum. My name is Amro and I'm the co-founder of a courierservice called Silver Sprint. We are based in the netherlands and we are doing pretty ok but I have a question about excel.

I have made a dashboard for my key performance indicators but I would like to calculate the average numbers.

Say I have this

Date Reveneu Costs etc.
1 100
2 150
3 230
4
5
6 140
etc

As you may have seen I didn't fill two days because those were the weekend.

So basically what I want to achieve is I would like to know what formulla to use to calculate the average per day but only on the days I generated revenue otherwise the stats will be polured.

Can someone help me out here?

Thanks!

Hi guys,

Anybody knows how to get a filter in SharePoint 2007 dashboard to send its
value to more than one Web Part on the dashboard? For example I want to be
able to select a year and when I click on "Apply Filters" it sends that
value to two other Excel Web Access Web Parts and the Key Performance
Indicators Web Part.

Thanks.

Hello,

I'm Scott Gordon with Webalo. We are releasing a new service, the Webalo
Mobile

Dashboard, and would like to invite you to be a part of our beta program.

The Webalo Mobile Dashboard allows you to easily deliver business
intelligence reports

including excel spreadsheets with data such as key performance indicators,
scorecards

and dashboards, to mobile devices - including BlackBerry, PocketPC, Treo
(700w windows

os), and smart phones.

We're hosting short online WebEx demos every Tuesday and Thursday. You can
sign up at

http://md.webalo.com . We're very excited to hear your thoughts and
feedback on our new

technology. After the demo you'll be given your own trial Mobile Dashboard
account.

You can contact me at sgordon@webalo.com.

Scott

I have put together a spreadsheet for recording scores on an inspection, and using /* by to average the scores as a % of 100, and then recording the score as a Key Performance Indicator (KPI) that is between 1 and 8 (1-5 be non compliance, 6-7 being partial compliance and 8-10 being compliance and compiance + figures)... All fairly simple so far, but...

It is apparent, given the list if items that scores are given for, that some of these items will not be applicable in some areas or types of situation, and therefore will not attact a score at all...

Therefore, I have to find a formula that will still do the math I want, but will also identifiy where cells are left blank and will then do the same math function but provide an average % score of 100 only on th cells completed (if some are left blank where they are not applicable) as opposed to providing a % of 100 only elated to the cells in which numerical data has been added (e.g. 5 cells - 3 completed and 2 left blank as not applicable - actual score on the three cells is maximum, so should retuern 100% but returns 60% as the function for the math covers all five cells)...

Any assistance would be appreciated....

Hi,,, I would really appreciate any quick help with this

I have two sheets tracking two key performance indices of people over time

what I need is a drop down menue where by selecting the person name it would display the plot with the two performance trends taken from the two tables, and the title of the plot would be the person name while the legend would point each performance index trend.

as an example i attached a sheet with similar simplified tables (can't put the real data) . here is a screen shot as well.

chart.jpg

Please any quick help would be appreciated

Can anyone tell me about how to make a dashboard for an MFI, we have the following data loan outstanding, repayment rate, loan disbursed, portfolio aging. I also want to rank the branches based on each of the parameters.

All the variabless mentioned above would be My Key Performance Indicators for the branches.

Hi,

when I open a spreadsheet I get the following message

Excel found unreadable content in Book_Name. Do you want to recover the contents of this workbook.

All the information I find says
This issue occurs if the following conditions are true:
The workbook contains a PivotTable that uses key performance indicators (KPIs).
The KPIs are created in the Analysis Services Business Intelligence Development Studio.
One or more of the KPIs have an expression in the Current Time Member property.

and the fix is
To resolve this issue, remove the expressions from all Current Time Member properties.

My issue is - the spreadsheet with Pivot tables is supplied to me and I have no idea how to do the fix. I am using Excel 2007 - can someone give me some advice on how to remove the expressions?

BTW - when I do this on another machine same version of excel I have no issue

thanks for any help

I am using a dictionary object from the MS Scripting Runtime library to store a series of arrays and perform operations on the array cells as necessary. There is a for loop to go through the process of creating all of these entries. My issue is that when using the .exists property, it is returning a True even before the item has been added. Closer debugging indicates that the key is being added to the dictionary at the beginning of the for loop, even though no .add command is used and will not be used until the end of the loop. The result is that the values in the arrays do not totalize as intended.

Code for the dictionary build has been included below. I have attached the Excel file which contains the appropriate module which calls that function


	VB:
	
Option Explicit[ATTACH]46705[/ATTACH] 
 
 
Function DictAppTable(Loop_Range As Integer, Key_Range As Range, Dim_1 As Range, Dim_2 As Range) 
     
     
    Dim DictTable As Dictionary 
    Dim AppElement(0 To 0, 0 To 1) As Variant 
    Dim iD As Integer 
    Dim strAppID As String 
    Dim strAppIDnum As String 
    Dim ProtoElement As Variant 
    Dim intSize As Integer 
    Dim iK As Integer 
    Dim MtrElement(0 To 0, 0 To 1) As Variant 
     
    Set DictTable = New Dictionary 
    DictTable.RemoveAll 
    DictTable.CompareMode = TextCompare 
     
    For iD = 1 To Loop_Range 
         
        strAppID = Key_Range.Rows(iD).Value 
        strAppIDnum = Right(strAppID, 5) 
         
        AppElement(0, 0) = strAppID 
        AppElement(0, 1) = Dim_2.Rows(iD).Value 
         
        MtrElement(0, 0) = Dim_1.Rows(iD).Value 
        MtrElement(0, 1) = Dim_2.Rows(iD).Value 
         
         
        If DictTable.Exists(strAppID) Then 
            DictTable(strAppID)(0, 1) = MtrElement(0, 1) + DictTable(strAppID)(0, 1) 
            DictTable(strAppID) = CombineTwoDArrays(DictTable(strAppID), MtrElement) 
             
        Else 
            ProtoElement = CombineTwoDArrays(AppElement, MtrElement) 
             
            DictTable.Add Key:=strAppID, Item:=ProtoElement 
             
        End If 
         
        Debug.Print DictTable(strAppID)(0, 0) 
        Debug.Print DictTable(strAppID)(0, 1) 
         
        Debug.Print DictTable(strAppID)(1, 0) 
        Debug.Print DictTable(strAppID)(1, 1) 
         
    Next iD 
     
    arKey = DictTable.Keys 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

My apologies for the previous post, as the pared down excel file which I posted had a slight error which prevented it from running


No luck finding an answer? You could always try Google.