In this situation why does the formula say “average” where it shouldn’t? It is the same with other functions like “sum” and “and”.
12.ods (15.4 KB)
.
.
.
What did you mean by ABS(AVERAGE(A$1-A3))?
That’s a mistake, i meant “:” instead of “-”.
I am confused! Why would one need to get the ‘average’ of a single cell value? The cell range (A$1:A1) is not a range, but a single cell (A1). Maybe calc gets ‘confused’ about this? Either remove the average function for ABS(A$1), or indicate a valid range like AVERAGE(A$1:A2).
But if you fill the formula into the next cell one row down, you get A$1:A2 there -and so on, and that’s often wanted and needed. ( I did NOT study the specific case here.)
1 Like
Yes, but the initial formula having AVERAGE(A$1:A1) is required to be in a cell of the first row (1), NOT the second row (2)! In this case the formula should start in cell B1, not B2 as shown.
(Quote emphasis added by me)
Why do you believe that?
1 Like
Using the posted calc images, as is, the result of your formula which includes …average(A$1:A1)… should display in B2; while the results of …average(A$1:A2) should appear in B3, which is not shown in posted images. In order for results of the formula …average(A$1:A2)… to display in B2, the formula …average(A$1:A1)… should be placed in B1 and filled down from there. Alternatively, ‘start’ the formula in B2 with …average(A$1:A2)… assuming you wish to display in B2 the formula result including A2 and all the previous values thru A$1.
Your assumption appears to be wrong.
The formula compares current entry (this row) to what happened before (averaging every row above, excluding this row), possibly to identify a trend or trend change, and returns a value (bonus, adjustment factor, whatever) based on that.
@emy1 is the one who can say for sure what is right or wrong, but your rather insistent statement that “[…] formula having AVERAGE(A$1:A1) is required to be in a cell of the first row” implies a technical/formal spreadsheet error, which this isn’t.
Looks like a bug to me. The formula parsing for the tip provider appears to be confused. Nothing wrong with your formulas as far as I can tell, only with the “helpful” tip popup.
The tip provided is for the function in the condition parameter of the IF() (same level in the formula hierarchy; previous argument to the IF() ) instead of providing tip for the context (level above; the IF itself) as we’d expect. Also, it only happens when the condition parameter contains a function.
I tested with MS Excel, which displays the contextually “correct” (expected) tip (describing where you are in the IF() function; one level up in the formula hierarchy).
Turning the condition backwards (like ... AVERAGE(A$1:A1)>A2 ...) makes no difference. Using other functions in the condition yields the same mistake (it supplies the first function it finds when backtracking), so the issue appears to be a loss of token/separator in the parser, or perhaps failing to maintain calculation hierarchy level in some situations.
3 Likes