1. J

    Calculated field in pivot table for % true or false

    Hi There, I need a calculated field to work out the "completed on time" % in my pivot table. When i do it manually, my formula is =(D5-B5)/D5. I want to insert a calculated field but only have it create one extra column with one result. Thank you,
  2. R

    Formula to Measure Time into Percentage Scoring

    Hi everyone! I hope you all have a pleasant day. I do not know if this is possible in Excel, but I would like to ask. We have a KPI for report submission deadline. The rule is, maximum time for submission is at 00.00 in the same day of working day. This would score a 100%. After exceeding that...
  3. H

    Workday formula - adjust for a zero occurrence

    Hi there, I have a formula that calculates the KPI days from the date deemed adequate. =IFERROR(WORKDAY([@[Date Allocated or Adequate]],[@[DPE KPI(working days)]],Holidays),"")............ returning the DPE KPI Target Date. If the KPI working days (returned from a vlookup) happen to be zero I...
  4. I

    need help with a sum formula

    Hi guys i have kpi results data below for one person <tbody> Name Alex win rate 56% renewed 5% answered 25% over all Score </tbody> I need to create a formula or come up with away to calculate the over all score. To get the over all score you need to score each kpi first and then...
  5. F

    Show target missed for last how many months

    Hi, I have a KPI dashboard where I have data from 2016 onwards. Each KPI data is organized in each row. KPI name is placed in A column and it's target in B column followed by monthly achievements. I would like to know for how many consecutive previous months the target is not achieved for...
  6. P

    Kpi dashboard

    Was just assigned a project at work to create a new executive financial dashboard. I know the forum is focused around specific questions. My question is does anyone know of free resources that could help me get started? I tried searching the web but all I see are companies selling their...
  7. S

    help required : how to retain previous cell value and showing new formula result

    Hi Folks.Hoping somebody here can point me in the right direction or provide a little assistance with a little quandary I've now found myself with.I run a daily KPI sheet for various sections of my workplace and we have a rather large workbook (30+ sheets) that runs myriad of statistics for a...
  8. S

    KPI/SLA calculation

    Hi all, I've got following issue: i need to determine KPI %. The KPI is about exceptance time, i've got two date/time column (ticket received (column L) and ticket accepted (column AC)) that i have to match, the threshhold is 4 hours (within 4 hours is positive, above for hours is negative)...
  9. S

    Copy and paste multiple ws from wb1 to corresponding ws on wb2

    Hi All,I'm relatively new to VBA and am reintroducing myself to it again now after a short hiatus.My current situation sees me compiling and producing reports based on data we get output from Oracle datatbases. Currently we run an Oracle report that contains multiple worksheets and export them...
  10. T

    KPI - Help required

    I need assistance on a formula to return the KPI scores based on the weighting on the below: The formula should read the number on under detail which in row 1 is 31 and return a kpi score from 0 in line 1 as per the scoring criteria. A <colgroup><col><col><col><col><col...
  11. B

    Sparklines -'Data source reference not valid'

    Hi, I've never used sparklines before and have been asked to include them in a dashboard. That has a large number of KPIs so I'm trying to create dynamic sparkline that I can create against the first KPI and copy against all other KPIs I've got a formula that will, upon error, show me its...
  12. D

    "The Slicer Cache already exists" error macros

    hi all, Im working on a macros involving different pivot tables and slicers. When I run the following: ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("sAPivot"), _ "KPI 1 Percentage").Slicers.Add ActiveSheet, , "KPI 1 Percentage", _ "KPI 1 Percentage", 155, 490, 145, 200...
  13. Z

    Auto-Generate Graphs with standardized data

    Hello, I am need to create about 60 graphs for some data I have in a workbook. As I said in the title, my data is standardized. The thing is, I simple have no idea how to approach this since I have never dealt with something like this. My data, normal range, goes like this: <tbody> KPI...
  14. Weeble

    Large amount of data and where to store it

    So daily I pull out a pretty large amount of rows with data for our weekly KPI reviews. The rows can be as long as 20k. What would be the most efficiant way of storing this amount of data? I was thinking of PowerPivot tables, but having problem with IT section to get PowerPivot in to Excel...
  15. F

    Creating scorecard using conditional formatting

    In "b2-B40" I have cells that have background color as yellow and is a 3 point deduction for not meetin Kpi, if background color is red than 7 point deduction for not meeting Kpi. In b50 would like code to calculate score by deducting Kpi error points from 100. Thanks for help,
  16. N

    Pivot Table Different Data type in the Same Column

    Is there any way to have different data type within the same pivot column. I have an report where the columns are in months and the columns are different KPIs. Based on the KPI, I'm switching between a number or a percentage. In the pivot chart, I would change the data type when I filtered to a...
  17. A

    Data connection opening read-only files on refresh

    Hi there First time posting a question – I’m usually a scavenger on this (and other) forum and always seem to find an answer to my question. This time, I’m stumped. This is my first time using data connections, but a brief outline of the setup. There are more connections, but the issue is the...
  18. B

    Change Custom KPI value to previous month Value if Current month value is not available

    Hello All, I have created the custom kpi using below measure. PlanArrows = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))And it works fine for me for perticular month...
  19. B

    KPI with different targets

    Hi, I have no problem setting up KPI using set value. However, I have different targets every month. I can't just change the set value every time I want to view the month. Please help. Any Idea how I can approach this? I tried adding target value to my raw data and sum that column as one of...
  20. Z

    Please Help-Urgent! How to calculate KPI score with target range?

    Hi, i'm in the midst of developing KPI dashboard. My table is as follow: <tbody> KPI Objectives weightage (%) Target Target Actual Achievement KPI Score (%) Actual Score (weightage) Base Stretch 1. New business 90 8 5 6 78% 8% 2. Get agreement signed 10 12 11 12 50% 8% </tbody> My...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back