My goal is to create a general formula that calculates the average percentages in a row, while ignoring the 100%, and continuing until a specified target column, which is an input, subject to change.
An example sheet is attached.
I was able to satisfy the first condition (ignoring 100) but not the second (stopping at a certain column number specified by I5).
I5 has the target column/level, I need the formula to update if this value changes.
I wrote this formula in I6: =AVERAGEIFS(A9:E9,A9:E9,"<>100",A9:E9,"COLUMN()<=I5")
And the column part is not working, how can I write a formula thats stops calculating the average at a column value specified in cell as a number?
Bookmarks