Forum Discussion
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 want the sum of the first three rows of the B column (not including column headers) and then divide that by 3. However, I want the cell to be dynamic so if I change the number to 5 it will then automatically take the sum of the first 5 rows and divide by 5. Additionally, if I put this value into D3, the second row of my table, I'd want the first number (3 or 5, or any number put in) of rows from that same row onwards instead of from the first row of the data set.
Any help would be greatly appreciated 🙂
5 Replies
Please try this and let me know if this is what you are trying to achieve.
In C2
=IF(D2="","",SUM(OFFSET($B$1,ROW(D2)-1,,D2)))and then copy it down.
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
Subodh_Tiwari_sktneerYes that's brilliant! Thanks so much!
Would you mind explaining how it works please, as I like to learn these things for future use?
