Forum Discussion

Tegzi's avatar
Tegzi
Copper Contributor
Jun 14, 2023
Solved

Calculation within official excel table (structured data)

Hello.   I struggle to find the right function to do this calculation. Let's say we have an official excel table - Table1 - that has 2 columns. The first column - with header "Value" - contains ra...
excel
  • Lorenzo's avatar
    Lorenzo
    Jun 14, 2023

    Hi Tegzi 

    Given you run 365. Does the following do it?:

    Edited:

     

     

    =LET(
  idx, INDEX([Value], SEQUENCE(ROW()-ROW(Table1[#Headers]))),
  flt, FILTER(idx, idx > 0),
  IFS(ISBLANK([@Value]),"", ROWS(flt)=1,[@Value], TRUE,AVERAGE(TAKE(flt,-2)))
)
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jun 14, 2023

Tegzi 

The "Value" is the header of column A (A1), you can modify the formula as follows:

=AVERAGE(LARGE(Table1[Value],ROW(Table1[Value])-ROW([@Value])+{0,1}))

This modified formula references the structured table name "Table1" and the column header "Value" to retrieve the values for the calculation.

Make sure to enter this formula in the appropriate cell of the "Calculation" column within your official Excel table.

If you are still experiencing issues or encountering errors, please provide more details about your table structure and any specific error messages you are receiving.

Please provide some additional information too: Excel version, operating system (win, mac, etc.), storage medium (Sharepoint, OneDrive, hard disk, etc.).

Tegzi's avatar
Tegzi
Copper Contributor
Jun 14, 2023
@ NikolinoDE

unfortunately the same result. I use Win11, Office 365 version.
I get the same #NUM! error in all cells. I get the same error back with this =AVERAGE(LARGE([Value],ROW([Value])-ROW([@Value])+{0,1})). Result is #NUM! in each cell.
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 14, 2023

    Hi Tegzi 

    Given you run 365. Does the following do it?:

    Edited:

     

     

    =LET(
  idx, INDEX([Value], SEQUENCE(ROW()-ROW(Table1[#Headers]))),
  flt, FILTER(idx, idx > 0),
  IFS(ISBLANK([@Value]),"", ROWS(flt)=1,[@Value], TRUE,AVERAGE(TAKE(flt,-2)))
)
    • Tegzi's avatar
      Tegzi
      Copper Contributor
      Jun 14, 2023
      Lorenzo

      Thanks, this works. I will need to analyze this to understand the full function, but it works 🙂
      Much appreciate it.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jun 14, 2023

        Tegzi 

        Glad this does what you expect & Thanks for providing feedback. A 365 alternative:

         

        =LET(
  pos, TOCOL(TAKE(IF([Value],[Value],NA()),ROW()-ROW(Table1[#Headers])),2),
  IFS(ISBLANK([@Value]),"", ROWS(pos)=1,[@Value], TRUE,AVERAGE(TAKE(pos,-2)))
)

         

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jun 14, 2023
    Is it possible to add a file (without sensitive data)?
    • Tegzi's avatar
      Tegzi
      Copper Contributor
      Jun 14, 2023

      NikolinoDE 

       

      Sure, this is a simple file. Obviously I'd like to use the formula for a bigger calculation to create charts. As soon as we figure out a workable formula for this, I'll be able to move forward with my plan.

       

      Thanks,

      TCS

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Jun 14, 2023

        Tegzi 

        =IFERROR(AVERAGE(INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A2:A6),0),0)),INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(A6:A10),0),0)+5)),"")

         

        File with example added.

Resources