Forum Discussion

akseeker's avatar
akseeker
Copper Contributor
Apr 06, 2020
Solved

Formulaic calculation of table rows

I'm trying to automatically update a chart that only displays the last 30 entries in a large (>600 rows) table.   I can't find a formula like "=NumRows(Table1)" to use in calculating the max number...
Charting
Excel on Mac
Formulas and Functions
  • ExcelExciting's avatar
    ExcelExciting
    Apr 06, 2020

    HI akseeker 

     

    You need to construct array formula, here is how it should work. If you are using office365 with new dynamic arrays calculation engine you can paste the formula straight away

     

    =MAX(ROW(Table1[Count Row])-1)

     

    If not paste the formula and press ctrl + shift + enter 

     

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

akseeker's avatar
akseeker
Copper Contributor
Apr 06, 2020

ExcelExciting you are correct, I was looking for a function that would return the total number of rows in a table in order to use the offset function. Unless you know the total number of rows in the table, how would you apply the offset function?

 

So, my question is, how does one (functionally) determine the number of rows in a table?

 

Thanks

ExcelExciting's avatar
ExcelExciting
MVP
Apr 06, 2020

HI akseeker 

 

You need to construct array formula, here is how it should work. If you are using office365 with new dynamic arrays calculation engine you can paste the formula straight away

 

=MAX(ROW(Table1[Count Row])-1)

 

If not paste the formula and press ctrl + shift + enter 

 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

  • akseeker's avatar
    akseeker
    Copper Contributor
    Apr 07, 2020

    Thanks ExcelExciting , now the next problem is using that info in the Chart:Data dialog:

    (attached a spreadsheet as example)

    The formulas currently read =Widgets!$I$71:$I$101 in the Y-Value field, and =Widgets!$A$71:$A$101 in the 'Horizontal (Category) axis labels' field.

     

    I've named the results of the "{=MAX(…)-30}" cell to ChartStartRow (H104), and the result of the {=MAX(…))} to ChartEndRow (H105). I concatenated those into a cell, with the calculated ranges listed out in text format, and named the cells XRange (J104) and YRange (J105).

     

    How do I get that calculation into the X and Y-Values fields in the chart data dialog box? I tried several approaches, but they all resulted in errors. It doesn't appear formulas [e.g. INDIRECT()] are allowed in the Select Data Source fields.

     

    I wanted to avoid VB…

     

    Thanks in advance!

  • akseeker's avatar
    akseeker
    Copper Contributor
    Apr 06, 2020

    Great answer @Faraz, that worked perfectly, thank you.

    PS: I looked in all the drop-down menus I could see on this page, but can't see an obvious way to set the question to 'solved'. I set it to Best Response, but don't see how to set it to 'Official Answer'. How is that accomplished?

Resources