Want chart that shows when people were active over the years

I need to create a chart that shows when people were active over the years. It should have the years on the horizontal axis and the people on the vertical axis. There would then be a horizontal line showing when the person was active, one line for each person. A plus would be to have a count of the active members for each year at the top, if that is possible.

Here is some sample data

Thanks for your response. I appreciate it. I believe I chose the wrong words. Perhaps I should call it a graph. I would like to have lines that show when the member was active. In general lay out, what you put on there is great, but I need just need it in graph form.

See if the attached is what you want. I've put this formula in E1:

=MIN(B$2:B$5)

to find the earliest year, and this in F1:

=IF(E1="","",IF(E1<MAX($C$2:$C$5),E1+1,""))

which when copied across will give you successive years up to the latest year in column C.

I've used this formula in E1:

=IF(E$1="","",IF($B2>E$1,"",IF($C2<E$1,"","x")))

which you can copy across and down as required - it will result in x in the cells which fall between the start and end dates. I've shown this as a solid bar of colour using Conditional Formatting with this formula:

=E1="x"

and setting both the foreground and background colour the same.

Hope this helps.

Pete

EDIT: Ah, I see you've already had a response along the same lines.

Pete

Thanks for the reply. I really need this in graph form and not an excel table form. Definition of the exact start and end years is less important. Essentially, this is being put into a report of membership which needs to occupy about the top half of an A4 page in portrait. I figured I would probably have the x axis display the years in increments of 5.

Okay, see if the attached is what you want. I've left the earlier one there.

Hope this helps.

Pete

Originally Posted by Pete_UK

Okay, see if the attached is what you want. I've left the earlier one there.

Hope this helps.

Pete

Pete, that is exactly what I need. Can you tell me how to make that for the actual dataset I have?

It's a 2-D stacked bar graph, using columns B and D (difference) as the source data. I've set the colour of the 1st series (B) to No fill, and I have manually set the starting year as 1970 to 2015 with an increment of 5. The Y-axis values are the names in column A.

I can't think of a way to get the numbers per year - maybe you can have a text box with those values to sit over the graph (and scale manually).

Hope this helps.

Pete

Thanks a lot Pete!

You're welcome - thanks for the reps.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete