• 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.

Charting dilemma

D

dpw_1

New Member
Hello,


I am trying to set up a chart of incidents aboard a cruise ship. Each voyage is tracked separately and can range from 3 days to 15 days. Our personnel enter each incident on a spreadsheet and we have a formula that determines the day of the incident and whether it occurs in the am or pm (1A would be Day 1 am, 1P would be day 1 pm etc.).


I am having two problems setting up the chart:


1.) We do not always have an incident in each time period so if I use a countifs formula to extract the data the missing time periods will not show up.


2.) I can't seem to set up my chart dynamically based upon the duration of the cruise (3 days to 15 days).


If I extract the data to a table that is set up for the maximum number of days (15) I can solve problem 1 but the resulting chart is misleading because the viewer does not know whether the outer days had no incidents or the cruise didn't last that long.


If anyone has any suggestions it would be greatly appreciated.
 
Hi ,


Can you check out the following worksheet :


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F!127


Narayan
 
Hi Narayan,


Thanks for the reply. I could not access the worksheet you included. The link takes me to my Windows Live shared space but I don' see anything there. Perhaps I am doing something incorrectly?


Thanks for the assistance.


Don
 
Hi Don ,


Sorry for the oversight on my part ; please note that the links actually represent only part of the website address , and clicking on the hyperlink is of no use. Please copy the entire address and paste it in your browser.


Narayan
 
Hi Narayan,


Happy New Year and thanks for the help. Unfortunately, I probably didn't do a good job describing my problem so your solution was a little off target.


To make tings easier I posted a worksheet with some sample data and two solutions that I tried but can't get to work. Hopefully after looking at this you will better understand what I am trying to acomplish.


Here is the link:


https://skydrive.live.com/redir.aspx?cid=aeb4708dd0ac979c&resid=AEB4708DD0AC979C!170&parid=AEB4708DD0AC979C!165&authkey=!AAjgpWt7T2npWB4


Thanks again for all your help.


Don
 
Hi Don ,


Thanks for posting a sample worksheet. I have some doubts :


1. If you say the problem with solution 1 is only that all 15 days are displayed on the X-axis , this can be taken care of by using the following range names :


Voyage : =OFFSET(Sheet1!$C$43,0,0,Sheet1!$B$6*2+1)


Passenger : =OFFSET(Sheet1!$D$43,0,0,Sheet1!$B$6*2+1)


Crew : =OFFSET(Sheet1!$E$43,0,0,Sheet1!$B$6*2+1)


2. More to the point is how you are going to derive the data for the chart in solution 1 , from the original Data Entry Table ? Will you be doing it manually ?


Narayan
 
Easiest way is to use Named Formula for the ranges and not directly access the ranges

I have setup 3 Named Formula

Crew

Employee

Onset_Code


then link the chart series to these named formula


You can see your data here

https://rapidshare.com/files/2601481831/TestGI.xlsx


Change the dates to say a 15 day cruise and the X Axis changes automagically


Enjoy
 
Thanks for the invaluable assistance and for taking the time to help out us excel novices.


I believe that I now have much more informative and user friendly spreadsheet.


Wonderful forum!!
 
You must log in or register to reply here.
Back
Top