I'll do my best.
I attempted to discern the criteria patterns.
I reasoned that numbers < 0 are a special case that only require the integer portion to be returned. So --LEFT(A1:A6,FIND(".",$A$1:$A$6)) only executes if < 0 returns TRUE.
The FIND function returns the character position of ".". LEFT returns that many left-most characters. The "--" before LEFT coerces the resulting text "numbers" to their numeric values.
Where numbers are > = 0 they remain unchanged.
All resulting values are "hidden" in an array. If you were to select just this part of the formula in the formula bar and press
the F9 function key IF(A1:A6<0,--LEFT(A1:A6,FIND(".",$A$1:$A$6)),A1:A6) you would see this array that is the combined result of that IF function {4;-202;6.9;-17;13;11}.
This array is passed to AVERAGE which returns -30.6833333333333. ROUND of course shortens that to -30.68.
If you are interested in an alternative formula you can skip the LEFT / FIND and use the TRUNC function. I had overlooked that option. I have almost never had cause to use it.
That shortened formula would be It is still array entered. TRUNC is the shorter way to return the left-of-decimal portion of negative numbers.
Also if you are interested there are two devices you can use to analyze formulas. One is the F9 key mentioned above and the other is an Excel feature called 'Evaluate formula'.
With one of the cells containing the formula active click on FORMULAS > Evaluate formula. A window appears. Click the EVALUATE button repeatedly and Excel will show
how it evaluates the formula step by step.
These two tools are excellent aids for dissecting formulas and self instruction. I recommend them whenever I can.
Did this help?
Bookmarks