1. 

   karmelito
   

   Member
   Joined: Feb '12
   Posts: 6

   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

   Posted 3 months ago #
2. 

   NARAYANK991
   

   Excel Ninja
   Joined: Oct '11
   Posts: 1,496

   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

   Posted 3 months ago #
3. 

   karmelito
   

   Member
   Joined: Feb '12
   Posts: 6

   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

   Posted 3 months ago #
4. 

   karmelito
   

   Member
   Joined: Feb '12
   Posts: 6

   Got a sample. Please help !!

   http://speedy.sh/Vkmh9/Test-Webs.xls

   Posted 3 months ago #
5. 

   NARAYANK991
   

   Excel Ninja
   Joined: Oct '11
   Posts: 1,496

   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

   Posted 3 months ago #
6. 

   NARAYANK991
   

   Excel Ninja
   Joined: Oct '11
   Posts: 1,496

   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

   Posted 3 months ago #
7. 

   karmelito
   

   Member
   Joined: Feb '12
   Posts: 6

   Hi NARAYANK991, cannot access the URL provided.

   Posted 3 months ago #
8. 

   NARAYANK991
   

   Excel Ninja
   Joined: Oct '11
   Posts: 1,496

   Hi ,

   Can you access Google Docs ?

   Narayan

   Posted 3 months ago #
9. 

   karmelito
   

   Member
   Joined: Feb '12
   Posts: 6

   Hi,
   sure, I can

   Posted 3 months ago #
10. 

    NARAYANK991
    

    Excel Ninja
    Joined: Oct '11
    Posts: 1,496

    Hi ,

    Check out here :

    https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdDloanRIRzk1dmt0cXpIZEpMaXhxVGc

    Narayan

    Posted 3 months ago #
11. 

    karmelito
    

    Member
    Joined: Feb '12
    Posts: 6

    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

    Posted 3 months ago #
12. 

    NARAYANK991
    

    Excel Ninja
    Joined: Oct '11
    Posts: 1,496

    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

    Posted 3 months ago #

RSS feed for this topic

Reply

You must log in to post.