averaging the 10 lowest values in 25 number data set while ignoring zero values

Please try this file.

in the given solution of raman change z to y
and it's array formula,you don't intentionally type the braces you have to hold down "CRTL" , "SHIFT" then hit Enter.... you'll notice the braces there if done right.

Formula:

Please Login or Register  to view this content.

Originally Posted by vlady

in the given solution of raman change z to y
and it's array formula,you don't intentionally type the braces you have to hold down "CRTL" , "SHIFT" then hit Enter.... you'll notice the braces there if done right.

Formula:

Please Login or Register  to view this content.

No need an extra 1*(INDEX() and also no need to put '+' sign in front the AVERAGE.

See post #4

Originally Posted by Teethless mama

=AVERAGE(SMALL(IF(A27:Y27<>0,A27:Y27),ROW(1:10)))

Press ctrl+shift+enter, not just enter

Using ROW(1:10) leaves the formula vulnerable to row insertions.

Better to use:

=AVERAGE(SMALL(IF(A27:Y27<>0,A27:Y27),{1,2,3,4,5,6,7,8,9,10}))

Or:

=AVERAGE(SMALL(IF(A27:Y27<>0,A27:Y27),ROW(INDIRECT("1:10"))))

Yes I know.

in the given solution of raman change z to y

just for the sake of the given solution of raman on why it did not work.
issue is
array entered and range is incorrect.

@ tony.. it's much better than row().

i'm referring to tony no 1 WITHOUT the ROW()

@ tony.. it's much better than row().

the second one still have the ROW(INDIRECT) as i mentioned on my quote above.

then that's the problem --> use array formula but being aware of what tony pointed out when he gave the formulas(since the range is too many)or use the volatile formula which as you said is most of us try to avoid.

Using ROW(1:10) leaves the formula vulnerable to row insertions.

...he will use his volatile formula as I mention in the previous post. Are they really better than 'ROW(1:50)'

Yes.

Don't be affraid of volatile functions!

Yes, if you have 1000's of formulas that use volatile functions they MIGHT slow things down. The only way to know for sure is to test it and see. Using a couple hundred or dozens of volatile functions will not impact performance.

If you can use an array constant then use that as your 1st choice.

If you know for certain that you will NEVER need to insert new rows then ROW(...) is acceptable. However, you should let the OP know how the formula would be impacted by inserting new rows at certain locations.

For example, if you insert a new row 1 then the formula becomes:


=AVERAGE(SMALL(IF(A28:Y28<>0,A28:Y28),ROW(2:11)))

That causes the formula to average the wrong values.

It doesn't make any difference if you make the rows absolute:

=AVERAGE(SMALL(IF(A27:Y27<>0,A27:Y27),ROW($1:$10)))

Insert a new row 1 and the formula still becomes:

=AVERAGE(SMALL(IF(A28:Y28<>0,A28:Y28),ROW($2:$11)))