• Scale while creating chart (Excel 2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Scale while creating chart (Excel 2000)

    Author
    Topic
    #390963

    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:-)

    Viewing 1 reply thread
    Author
    Replies
    • #696521

      hello 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

      • #696534

        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.

        • #696543

          hello 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

          • #696551

            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.

            • #696555

              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

            • #696803

              Peter,

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

    • #696546

      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)
      
    Viewing 1 reply thread
    Reply To: Scale while creating chart (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: