Forum Discussion

Drakerla's avatar
Drakerla
Copper Contributor
Sep 17, 2019

Taking values from certain rows based on data input

So I'm trying to get a value into the column "Cat A Tied" (C column). This value depends on the number in the "# People Tied With" column (D Column). In this case, cell D2 has the number 3 in it. I w...
Columns
data
excel
Formulas and Functions
Rows
Tables
Drakerla's avatar
Drakerla
Copper Contributor
Sep 17, 2019

The only change needed is to divide that SUM value by the value in cell D2. But thank you so much for your help!

 

=IF(D2="","",SUM(OFFSET($B$1,ROW(D2)-1,,D2)))/D2

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 17, 2019

    Drakerla 

    Another variant

    =IFERROR(SUM(INDEX(B:B,ROW()):INDEX(B:B,ROW()+$D2-1))/$D2,"")
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Sep 17, 2019

    Drakerla 

    It seems I missed the division part but that was easy to fix and glad you did it right.

     

    The range passed to the SUM function is derived with the help of OFFSET function in which the anchor cell is $B$1 (which is absolute reference and will always be the same when you copy the formula down) and the row is offset by ROW(D2)-1 which returns the first cell in the sum range i.e. if the formula is placed in the second row, ROW(D2)-1 will return 1 and hence the first cell in the sum range would be one row offset from the anchor cell B1 which would be cell C1 (if you wish you may remove the D2 from ROW(D2) and make it just ROW() and it will work in the same way) and then the height is determined by the number in the D2, If you evaluate the formula in D2, it would be easy for you to know how this formula works.

     

    If that takes care of your original question, please accept the post with the proposed solution as a Best Answer/Response to mark your question as Solved.

Resources