data selection in a row can variable as per data availability

[usamampk]

Good evening friends,

i have some data in a row ("row 8" in this case), this data is actually obtained through formula from previous rows. consider the "row 8" is

Row (8) = a b c d e f g h i j k l
Row (9) = l a k b j c i d h e g f


i am using a formula in next "Row (9)", which is arranging the data in "row 8". As per the current "row (9)" formula, i have to make manual selection as per the "row 8" data (number of cells with data). Row 9 formula does not work if "Row 8" data varies (cells with data increase or decrease). can the "Row (9)" formula be changed in a manner that it also consider the data (number of cells with data) available in "Row 8"? the sample file is attached. thanks in advance

[AliGW]

Do you mean this?

=SORTBY(E8:P8,-ABS(COUNTA(E8:P8)-AVERAGE(COUNTA(E8:P8))+0.1))

[usamampk]

yes exactly, AliGW. or any other method to consider data cells in "Row 8"

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[usamampk]

the formula is considering the data from E8:P8, if data changes to E8:Q8, then the formula will not consider the cell P9. the formula must also then switch to E8:Q8.
your this formula
=SORTBY(E8:P8,-ABS(COUNTA(E8:P8)-AVERAGE(COUNTA(E8:P8))+0.1))
not solving the purpose
Thanks

[usamampk]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

the formula is considering the data from E8:P8, if data changes to E8:Q8, then the formula will not consider the cell P9. the formula must also then switch to E8:Q8.
your this formula
=SORTBY(E8:P8,-ABS(COUNTA(E8:P8)-AVERAGE(COUNTA(E8:P8))+0.1))
not solving the purpose
Thanks

[Bo_Ry]

Please try

=LET(a,E8:Z8,b,FILTER(a,a<>""),c,SEQUENCE(,COLUMNS(b)),SORTBY(b,-ABS(c-AVERAGE(c)+0.1),))

[AliGW]

Just extend the range to cover the maximum, e.g.

=SORTBY(E8:P8,-ABS(COUNTA(E8:ZZ8)-AVERAGE(COUNTA(E8:ZZ8))+0.1))

[usamampk]

Please try

=LET(a,E8:Z8,b,FILTER(a,a<>""),c,SEQUENCE(,COLUMNS(b)),SORTBY(b,-ABS(c-AVERAGE(c)+0.1),))

Thanks a lot. this solve the issue.