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)

     

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

    • NeoGen's avatar
      NeoGen
      Copper Contributor
      Jan 31, 2023
      Thank you dscheikey,

      Is it possible to do the same with either cells F16:H18 or B16:D18 which are the cells I would like blank if all the cells in B2:J8 are zero?

      Many thanks in advance

      Nick
      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        Jan 31, 2023

        NeoGen 

        J16 is the alternative to B16 and N16 is the alternative to F16.

        I just left the old calculations and created the alternatives with IFERROR() next to them. I thought you would recognise that.

         

Resources