• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problems to plot several series of number

K

karmelito

New Member
Hi,

I'll try to explain my problem. I'm managing several Websites. The application availability, application performance, and other KPIs variables related to them are provided in an Excel file with the following structure:


Date 1 Date 2 Date 3

Appl. Availability Appl. Performance Appl. Availability Appl. Performance Appl. Availability Appl. Performance

Web1 x% x% x% x%

Web2 x% x% x% x%

Web3 x% x%


I'm trying to plot these values in order to identify trends and managing SLA but I don't know how. I'll try to explain the problems I've found:


a) The values related to each KPI are not in contiguous cells, so the only way I've found to catch them is using named range. The problem is when I'm defining the cells of the named range: it exceeds the length of 255 chars and return an error. Example: Web1_appl_availability=Sheet1!A2;Sheet1!A4;Sheet1!A6;...


b) How to manage the dates? . The dates are the first row grouping as many cells as KPIs variables are defined.


Please, if you have any idea, it will be welcomed. I have no idea how to manage these data.


Thanks a lot.


Karmelo
 
Hi ,


The following link will help you with your first problem viz. how to use a named range to refer to non-contiguous cells :


http://www.thecompanyrocks.com/how-to-name-a-large-range-of-non-contiguous-cells-in-excel/


Narayan
 
Thanks NARAYANK991, the definition works that way !!


But there must be something wrong once I use that named range in the chart. Depending on the number of values, it returns errors. Something really weird.


Any other ideas where the error can be?


An how managing series by date according the structure I have?


Thanks a lot
 
Hi ,


Can you please clarify the following ?


1. You have only 3 KPIs , KPI1 , KPI2 and KPI3. If not , what is the upper limit on KPIs ?


2. You have a range of dates ; is it only 3 ( date1 , date2 and date3 ) or is it more ?


3. You have only 4 webs , Web1 through Web4. If not , what is the upper limit on webs ?


4. The horizontal axis will be the dates.


5. Each series will be defined by a combination of KPI and Web i.e. KPI1 and Web1 , KPI1 and Web2 , and so on.


Are all of the above correct ?


Narayan
 
Hi ,


Can you check the following link ?


https://skydrive.live.com/?lc=16393#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21157


Please do not click on the hyperlink ; copy + paste the entire address in your browser.


I have defined the following ranges : Dates , KPI1 , Web1 , Web1_KPI1 and Dates_KPI1.


If you think this is OK , then it can be extended to the remaining 11 series.


Narayan
 
Hi,

I get an error in the graph


Please, find a more specific sheet with the details


http://www.speedyshare.com/file/vqyPr/Web-Statistics.xls


Thanks a lot
 
Hi ,


There is no problem other than the fact that the dates in row 1 are in merged cells ; this will not work.


When the cells B1 , C1 , D1 and E1 are merged and the date February 1 is in the merged cell , the fact is that only B1 contains the date ; the other cells C1 , D1 and E1 do not.


You will have to unmerge the merged cells , and have the right dates in all of them , so that they can be used on the X-axis.


Narayan
 
You must log in or register to reply here.
Back
Top