Forum Discussion

NeoGen's avatar
NeoGen
Copper Contributor
Jan 27, 2023
Solved

Identify values and then display the content of the cell.

I am trying to set up a questionnaire that self populates the results. I have the answers given to 9 questions of 7 sections in a table (table is 9x7) Seen below: Each column is then summed, a...
excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Jan 27, 2023

    NeoGen 

    If your data including the headings are in cells B1:J8. The totals in row 9 then this function should work. See also the attached example file.

    =INDEX(SORTBY(B1:J1,B9:J9,-1),SEQUENCE(3,3))

    A little more modern with the new WRAPROWS() function:

    =WRAPROWS(SORTBY(B1:J1,B9:J9,-1),3)

     

NeoGen's avatar
NeoGen
Copper Contributor
Jan 30, 2023

dscheikey 

 

I had a look at this, and I don't think I explained it well, what I am looking for is if this grid shows all 0 (zero)

 

Then this table would be blank

 

The complicated bit is, that it should only be blank if all the rows and columns are blank and every other time show the results.

 

Hope that helps explain it a bit better.

 

Many thanks in advance for your help

 

Best wishes

 

Nick

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Jan 30, 2023

    NeoGen 

    If you set an IFERROR() around it, the #CALC error is also gone!

     

    • NeoGen's avatar
      NeoGen
      Copper Contributor
      Jan 30, 2023

      dscheikey 

       

      Many thanks for your quick response, as a novice to this, where would I put the IFERROR()?

       

      Best wishes

       

      Nick

Resources