Formula to chart every nth row data using named ranges (without using another column)

  • Hi!


    This post consists of 2 inter-related questions:


    1. I have a column of data as below from which I wish to plot a chart of every nth row, using Excel's named range as my series. The named range should be where I should be able to enter a formula to pull data every nth row.


    Data
    10
    26
    18
    16
    20
    12
    24
    5
    25
    14
    9
    23
    17
    14
    29
    7
    4
    13
    25


    For example, if the data is arranged in column B, and I enter a value of 3 in cell C1, I want the chart to plot only data in the 1,3,7,10th row and so on...
    So, from the above data range, if I entered 3 in cell C1, I want excel chart to plot the values 10,16, 24, 14 ....


    One way of doing this is to first pull the value of every nth row into another column and then plot the chart using this new range. However, this is needless duplication of data, (and given that I will be plotting a no. of series and a no. of charts, this is creating a lot of duplication) and slowing my charts down.


    How do I plot every nth row using Excel's named range as my series with a formula to define the values to be pulled in the series?


    2. Another question is that if I entered 3, then I wish to sum up values for every 3 rows and then plot a column chart having sum of every 3 rows in a column.
    For example, using the above data, my bars will be plotting values of 54 (10+26+18), 48 (16+20+12), 54 (24+5+25), 46 (14+9+23).....


    Is this also possible with named range as my series with a formula to define the values to be pulled in the series?



    Sample file attached.


    Regards,


    Naira

  • Re: Formula to chart every nth row data using named ranges (without using another col


    Hi Naira,


    Quite often people get an idea in their head about how a solution should work and this is why moderators will quite often suggest to forget about what you think the solution should be, and just state what the problem is. It can happen that the actual solution can be far different to what you might have been thinking.


    You have mentioned "Named Ranges" in your thread title, but for me personally, I dont see any specific reason or benefit to do this. But thats just me. I'm assuming here you dont care what the solution entails, so long as it actually works :)


    I took a different approach using two helper columns with some fairly basic formulas and Pivot Charts to display the charts you need. No named ranges. You can basically hide these Helper columns and Pivot tables (Column D to L), to just leave the Pivot charts in place... job done. The only code is on the change event for the worksheet to just refresh the pivot tables when you change the value in C1.


    I also added conditional formatting to the source data so that I could actually "see" what I was supposed to be displaying or summing in the chart.


    The "secret" in the Pivot Chart is to only Pivot on "TRUE" data (from column "D") in the Pivot Filter for the Pivot table... Thats how I did it anyway :) Of course, more than likely not the only way to do it.


    See attached
    Regards
    Ger

    Files

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Formula to chart every nth row data using named ranges (without using another col


    Hi Ger,


    Thank you for your reply.
    However, I do care how we arrive at the solution, since as I had specified in my original Post, I already have a working solution

    Quote

    One way of doing this is to first pull the value of every nth row into another column and then plot the chart using this new range. However, this is needless duplication of data, (and given that I will be plotting a no. of series and a no. of charts, this is creating a lot of duplication) and slowing my charts down.


    Unfortunately your solution is even more cumbersome than what I am already doing.


    Fortunately, I have found that Excel has usually not disappointed the wildest fantasies that come up in my head. Its just been a matter of time and looking hard enough. In one case, I found solution to a question posted on this forum, after two years.


    Fortunately again, I have found a working solution to my current question and in exactly the form that I was looking for (although I am still looking for help to fine-tune the solution).


    The formula in the named range for non-contiguous data arranged in a column looks like this:

    Code
    =N(OFFSET(Sht!$B$3,0,(COLUMN(Sht!$B$3:INDEX(Sht!$2:$2,8))-2)*Sht!$A$1))

    with the reference cell where I enter the no. of columns to skip being cell A1


    However:

    1. When I convert this to a row formula

    Code
    =N(OFFSET(Sht!$A$3,(ROW(Sht!$A$3:INDEX(Sht!$2:$2,8))-2)*Sht!$A$1,0))

    my charts are plotting only the first 2 data points. Anyone has any ideas how to fine-tune this so that all data points are plotted with data in on-contiguous rows?

    2. I am also looking for a solution to further fine tune this formula to sum up groups of n cells and plot a column chart with the summed up values.


    - New sample file showing the working solution for plotting data in non-contiguous columns attached

    Regards,


    Naira

  • Re: Formula to chart every nth row data using named ranges (without using another col


    Great - thanks Naira - thanks for following up with your own solution, although I dont personally see how that will work, but I probably dont understand the problem sufficiently.


    All I was saying about the Named Ranges approach was to try and not assume that named ranges are the only solution to your problem. So, from my perspective you are looking for a solution that is NOT slow (because from your posts above, this is the only issue you have with your current solution(s) ). You have gone ahead and assumed the solution to your current problem lies in named ranges, and I dont think this is the best problem solving approach to finding a solution to your problem. Dont take that wrong way.


    Personally I would look at trying to understand the performance issues in your current solution and look at fixing that, with either better helper columns, or using VBA. Try charting all series for example, and then hiding some series afterwards. But, different strokes for different folks... and who knows, you might even get the named ranges to work as expected.


    Good luck with your hunt. :D


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Formula to chart every nth row data using named ranges (without using another col


    As far as plotting the rows.. try changing your row Named Formula to:


    [COLOR="#0000FF"]=N(OFFSET(Sht!$A$3,(ROW(Sht!$A$3:INDEX(Sht!$A:$A,21))-3)*Sht!$A$1,0))[/COLOR]


    and for the Summing use this Named Formula:


    [COLOR="#0000FF"]=SUM(OFFSET($A$3,((ROW(Sht!$A$3:INDEX(Sht!$A:$A,21))-3)*Sht!$A$1),0,$A$1,1))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Formula to chart every nth row data using named ranges (without using another col


    Tried NBVC's solution and first solution works great with charting individual rows.


    Second formula however is a bit off the mark. Lots of trial and I still don't seem to be able to get it to work...


    What I was looking for is that if I entered 3, then I wish to sum up values for every 3 rows and then plot a column chart having sum of every 3 rows in a column in each individual column.
    For example, the desired result when using the data range given in my original post (and entering 3 in cell A1), my chart should be plotting bars values of:


    Bar 1: 154 (10+26+18) (viz. sum of row nos. 1,2,3)
    Bar 2: 48 (16+20+12) ,(viz. sum of row nos. 4,5,6)
    Bar 3: 54 (24+5+25) ,(viz. sum of row nos. 7,8,9)
    Bar 4: 46 (14+9+23) (viz. sum of row nos. 10,11,12)


    However, the formula =SUM(OFFSET($A$3,((ROW(Sht!$A$3:INDEX(Sht!$A:$A,21))-3)*Sht!$A$1),0,$A$1,1)) is summing up the values of every 3rd row and giving me a single bar having the sum of every third row, i.e. I am getting a single bar summing up values of row nos. 1,4,7,10...


    Interestingly, when I enter the formula =SUM(OFFSET(sht!$A$3,((ROW(sht!$A3:INDEX(sht!$A:$A,21))-3)*sht!$A$1),0,sht!$A$1,1)) in a cell in excel and drag it down, I get the desired resulting values. But it does not seem to be working when entered as a formula in a named range for mapping a chart.


    Could you please help me figure out what am I missing?


    Sample file showing result of the formulas when used as a named range attached for reference.

  • Re: Formula to chart every nth row data using named ranges (without using another col


    Yeah, I should have tested the charting. I only tested the formula in the sheet, as you did. I am not sure it will work. You will need to reference a column of the results (i.e. you will need the helper column... I think).


    The first formula references every 3rd cell (i.e. the result is an actual cell address) and therefore you can plot based on the referencing. The second formula gives numeric result and does not yield cell addresses for the series to reference, and so you don't get expected results.


    I am not a charting expert, and not sure if there are other ways around it, but I don't believe there is without side work.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Formula to chart every nth row data using named ranges (without using another col


    I removed the last part of my previous comment as it was incorrect...


    You can try this as a helper that will hopefully reduce load and you will be able to use my first Named Formula adjusting for column B instead of A as references.


    In B3 try:


    [COLOR="#0000FF"]=IF(MOD(ROW()-ROW($A$3),$A$1)+1=1,SUM(OFFSET(A3,,,$A$1,1)),"")[/COLOR]


    copied down. Then create Named Formula using


    [COLOR="#0000FF"]=N(OFFSET(Sht!$B$3,(ROW(Sht!$B$3:INDEX(Sht!$B:$B,21))-3)*Sht!$A$1,0))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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