Reducing un-used chart variables, for paramount scaling

    forum.ozgrid.com/index.php?attachment/42755/


    I have a chart that displays all variables, as normal, but if some of the variables are not being used
    it makes the chart seem un-tidy, harder to read and includes space not required, thus reducing the viewable span of the chart or graph. I was hoping there may be a couple of tricks and a function or two to solve this problem.. as in attachments.
    ash

    Re: Reducing un-used chart variables, for paramount scaling.


    Replacing the zero values with #N/A will stop them being plotted. Excel doesn't plot error values


    Use something like


    =IF(A1=0, NA(),A1)

    There are three types of people in this world.
    Those who can count and those who can't.

    Re: Reducing un-used chart variables, for paramount scaling.


    Thanks Neale
    the variables adjust to N/A but the graphs still show large sections of nothing.


    Must be because the cells are selected whatever is in them

    Re: Reducing un-used chart variables, for paramount scaling


    To make the chart smaller you need to hide those rows wit zero amounts


    hidden cells don't display at all


    N/A are not plotted

    There are three types of people in this world.
    Those who can count and those who can't.

    Re: Reducing un-used chart variables, for paramount scaling


    Not sure if this is a viable solution for you since I don't know the complete details about your data.


    However, to dynamically plot just the contiguous cells that are greater than zero you could use a helper column to facilitate a named range that describes the cell and is used as the inputs for the chart series.


    See the attached example.


    Column-A is used as the helper column with this formula: =IF(AND(C7>0,D7>0),ROW(),"")


    The following named ranges were created:


    Chart_Labels =OFFSET(Sheet1!$A$6,MATCH(MIN(Sheet1!$A$6:$A$29),Sheet1!$A$6:$A$29,0)-1,1,COUNT(Sheet1!$A$6:$A$29),1)
    BHP1_Data =OFFSET(Chart_Labels,0,1)
    BHP2_Data =OFFSET(Chart_Labels,0,2)


    To see how the named ranges are used in the chart, right click on the chart and choose Select Data from the context menu.
    After the dialog is open, select BHP 1 and chose Edit Series and review the contents of the Series parameter boxes.


    Initially these boxes show something like Sheet1!$C$7:$C$29.
    The range range reference is replaced with the named range so that it become Sheet1!BHP1_Data
    After Saving the workbook Excel converts this to show as =Oz_160883.xlsx!BHP1_Data


    See this page: Dynamic and Interactive Charts
    forum.ozgrid.com/index.php?attachment/42849/


    Another option (easiest and most flexible) is to use a Pivot Chart. See Sheet8 of the attachment.

Participate now!

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

Register Yourself Login