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

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

      • 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!

Resources