# Pivot, Summarize values by Maximum, 24-hour clock

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Wow looks great, sorry for confusion. How did you change the axis or know what to change the y axis to? That is exactly what I wanted, I just want to know how you got those decimals for fixing the y axis. Very interesting.

Why couldnt we just use col D and E for the chart?

Anyway, I know this is wishful thinking, but is there a way to stack some or all the components within each bar for each date?

• Re: Pivot, Summarize values by Maximum, 24-hour clock

XL uses decimal to represent time, and there are 24 hours in a day. So, to calculate what one hours equals is:
=1/24

8 am of the next day is:
=32/24

and so on.

Col D and E have the date in them, which has a value of itself. Each day along the chart was also adding 1 (aka, 24 hours) to your values. This is why the first chart was rising upward.

To stack the components, we'd have to know duration, which is where I was a few posts ago. I can make a guess at the calculation...see attached for how the chart turns out. I shifted the y-axis (the sum of duration is much smaller than max)

## Files

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Still having trouble with the axis, I have it the same as you:

Min .83
Max 1.5
Major unit .0416
Minor unit Auto.

where am I going wrong? Is there another setting?

Also, this one clock goes to 24, which is kind of tough since the times are supposed to go through the night.
By stacking components I mean, Row 1 of usain bolt is one component so that would be sanctioned off in the bars, and so forth. Or even have the first 3 rows sanctioned off in the bar, is that possible to do?

Let me know if this is duration you need , check the attached sheet. I also included sample stages Note: not every runner has the same stage. So the stacks bar for usain bolt on the 8th would becomposed of : NY, Phil, Wash Total, LA total, ending at 06:30 am.

## Files

• Re: Pivot, Summarize values by Maximum, 24-hour clock

When you add the components together, you are no longer starting at 8 pm in terms of the day, you literally only have 3 hours with first component. So, if you tried to shift the y-axis now, the values would be too low to see.
I think overall you are confusing the issue slightly by mixing up the concepts of duration w/ time of day. Thanks for the calculated duration, it helps a little.

Take a moment to think about how stacking the individual components would look on a chart, and what they would be telling you...actually, overall, I'm not even sure what story you're trying to tell. That might be a better question.

## Files

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Maybe you have a better way of showing it, I was just thinking of each runners times for the date shown in comparison with the expected time. The stages/components were just to shown the progress and how long each checkpoint took, maybe there is better ways of showing this in a presentation form. Your right the duration is not really what im trying to measure, I need the time of the day they finish . ex: usian finished at 6:30 but expected to finish at 6:25 and starting at 10pm

I prefer the chart where you had the hours 8 pm to noon the next day , that was really good. how did you alter that axis btw?
As for the stages, maybe you have some good ideas on how to present that.

• Re: Pivot, Summarize values by Maximum, 24-hour clock

I'm very curious as to how you got the 8 pm to noon the next day on the axis

• Re: Pivot, Summarize values by Maximum, 24-hour clock

For changing the y-axis values, I calculated what the decimal equivalent of 8 am today and noon tomorrow would be.
A) 8 / 24 = 0.333333
B) 32 / 24 = 1.3333333

I then set those values as the min/max. To get a nice spacing of every 2 hours, I set the major units to be 2 hours (aka, 2 / 24 = 0.0833333)

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Quote from Luke M;767766

For changing the y-axis values, I calculated what the decimal equivalent of 8 am today and noon tomorrow would be.
A) 8 / 24 = 0.333333
B) 32 / 24 = 1.3333333

I then set those values as the min/max. To get a nice spacing of every 2 hours, I set the major units to be 2 hours (aka, 2 / 24 = 0.0833333)

Hey Luke, still getting this as I set it:

[ATTACH=CONFIG]68637[/ATTACH]

## Images

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Ah, I see. The absolute value of the scale is right, need to just change the format. On the number menu, change format to be:
h:mm

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Hi Luke, sorry to bother you so much, it was that format

[ATTACH=CONFIG]68638[/ATTACH]

## Images

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Not quite. In your pic, note that the "h" has square brackets around it, like this
[h]:mm

That means that you want total hours, not just the 24 hours in a day. I suggested changing it to:
h:mm

w/ no brackets.

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Hey Luke! it worked, thanks. Also changing the format to time may have helped me.

if you have any energy left, could you recommend a way to incorporate a stacked cluster bar with the stages in each bar ? The stages would be max of each stage.

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Hey Luke,

For times that seem to be the same, are giving me these huge bars. I guess because the start and finish time are the sane, are their work arounds?

## Images

• Re: Pivot, Summarize values by Maximum, 24-hour clock

If the times were the same, bar should be non-existent as the calculation would be x-x = 0.
Can you upload the workbook? We've done so many different files, I've lost track of where we're at.

As for the cluster-stack chart, I don't think that can be done with a PivotChart. Would need to build it as a regular chart, and be clever w/ how you layout the data.

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Hi luke,

Look under 'Luke OZ' you can see the values quite high.

## Files

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Also luke, is there a way to get accurate data labels on this? If I put labels it ends up showing times such as 30:30 when I want it to say for exmaple 6:30 am.

• Re: Pivot, Summarize values by Maximum, 24-hour clock

It's large because the value is large. Looking in raw data are row 15, in col E we see that Ozzy didn't finish till almost midnight of the 2nd day, and the chart only goes to noon of the 2nd day. So, two questions:
1. Is the data right?
2. If it is, how do you want to handle it, e.g. increase scale of chart? If not, what should it be?

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

The Chart takes max time no? So wouldnt it be row 16?
1) the data is right
2) It shoudnt increase scale, row 16 has same start and finish, (23:59) so it should show that in the chart as it finished before 12..

I think your right, you mean because the date is 8th and it didnt start till the 9th right? So the chart is actually correct. I guess to show it just increase scale of chart.

Do you know of any ways I can creatively show the stages?

• Re: Pivot, Summarize values by Maximum, 24-hour clock

Both row 15 and 16 have the same time, aka they are both the max. For purposes of your chart, the time in col D isn't used. You would need that if plotting duration instead of finish times.
Sounds like tho you agree, data is correct, should be plotted at 23:59 of the 2nd day, so it's off the scale in chart.

For the chart, I don't think we'll be able to to it stacked, and I'm not sure what it would tell us. How about this chart? Stretches things to right, but now we can see each day broken out by the stages.

## Files

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Pivot, Summarize values by Maximum, 24-hour clock

That could work, just gets a little sloppy with all the stages, I can work with it though.

You see the expected times, is there ways I could get a few lines horizontal, such as one at like 5:30 for usain bolt, and just just add in custom lines I desire for each runner?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!