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 of rows, then with a formula (in the 'choose data' dialog) set the chart range to '=NumRows(Table1)-30:NumRows(Table1), for instance.

 

Does such an animal exist? Can counting table rows be accomplished without resorting to VB?

 

Now, I select the chart's value line, the data it's based on is enclosed/highlighted, and I manually move the data targets down a row to only include the last 30 rows; be neat to not have to do that manually.

 

Thanks!

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

5 Replies

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Apr 06, 2020

    Hi akseeker,

     

    You seem to referring some custom function NumRows. There is no built-in function.

     

    What i understood you wish to get the last 30 day transaction from the range, Well I can suggest you try building using OFFSET function.. If you still facing issue attach a sample file.

     

    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

Resources