Scale while creating chart (Excel 2000)

Topic

New Reply

I am creating a chart based on hours to complete a task. I would like the chart to show days instead of hours. Right now I have to copy the spreadsheet and divide each cell by 8 to convert from hours to days. Is there a way that I can have the “divide by 8” factor a part of my chart? Thanks in advance:-)

Reply | Quote

Replies

Dan

Sorry if this sounds silly, but I am asking to get clarifications…

Do you enter the data in hours, or days? If hours, then why not enter days?

If in hours, you could have a mirror-range, a range that is just the same size and shape, with a formula that divides by 8. Then chart based on that range.

Anything you enter in the main range will be divided into 8 in the mirror range and since the chart is based on the mirror, you get your days.

Hope this helps.

Wassim

Reply | Quote

Wassim,

Thanks for your reply.

I usually use the data in hours, and I get all data in hours, so I enter it that way. But occasionally, I want to report days instead of hours.

How do I develop a mirror-range? That sounds like what I need. Would the mirror-range update as I add or change data in the Hours sheet?

Appreciate your response.

Reply | Quote

Dan

OK I have attached a workbook example for you.

How to make a mirror range is the name of the workbook, and check the formulas so that you can understand the mirror system. Each cell will have a cell in the mirror range.

And yes the range will update automatically if Calculations is set to automatic under Tools | Options | Calculations Tab.

Hope this gives you a starting point.

Wassim

Reply | Quote

Wassim,

Thanks a lot. I see what you have done. I think that I will try to have the mirror range as a different worksheet.

Reply | Quote

The attached is a variation on Wassim’s mirror technique… I’ve added a drop down to allow you to switch between Hours/Days. Point your chart at Wassim’s suggested mirror range and when you change between hours and days, your chart will automatically update.

Regards
Peter

Reply | Quote

Peter,

Thanks so much for your refinement of the mirror technique. That is really slick 🙂 Works like a charm!!

Reply | Quote

Assume that the times are in column C, and that row 1 is a header row. Therefore the first time is in C2. Select an empty column and enter the formula below in row 2 of that column:

=IF(C2="","",C2/8)

Copy that formula down as far as is necessary. Use this column to do your plot.

If you have empty cells in the time column and don’t want those plotted as zero values, then modify the formula to:

=IF(C2="",NA(),C2/8)

Reply | Quote

Legare,

I appreciate your response. I see how flexible this mirror range can be 🙂

Reply | Quote