9

First let me introduce an example:

enter image description here

I have a table of raw scores for players each day, it's call "Raw Score".

enter image description here

I also have this table of modifiers that I will apply to each players' daily raw score, it's called "ScoreMod".

enter image description here

I also have this table that calculates their total effective scores. It's called "Total".

To calculate each player's daily effective score (raw scores + modifiers), I use the following function.

=LET(
MyDate,    FILTER(RawScore[Date],RawScore[Name]=Total[@Name]),
MyRawScore,FILTER(RawScore[Score],RawScore[Name]=Total[@Name]),
MyModifier,XLOOKUP(
    MyDate,
    FILTER(ScoreMod[Date],     ScoreMod[Name]=Total[@Name],0),
    FILTER(ScoreMod[Modifier], ScoreMod[Name]=Total[@Name],0), 0),
MyModifier + MyRawScore)

This function works just fine, as show in the picture. Tom's effective scores are correctly calculated.

enter image description here

Then I define the above function as EffectiveScore.

However, when I try to use =SUM(EffectiveScore) for Tom's total score in the Total table, the result is incorrect, as it becomes 300.

But if I use =SUM(+EffectiveScore) for Tom's total score, then the result is the correct total score, 280.

My question is, why the formula without the + can not get me the correct answer, and why adding a + gets me the right answer?

Link to the document: https://1drv.ms/x/s!At1ltp8PtMK-gpsP2cOPZStP89ozpw

toaruScar
  • 401
  • 4
  • 6
  • 5
    Very interesting. Looks like `XLOOKUP` needs additional coercion (for example, with the unary plus operator) to operate over an array, though **only when used within a defined name**. `=SUM(XLOOKUP({"a","b"},{"a";"b";"c"},{1;2;3}))`, used within the worksheet, correctly returns 3, though if `=XLOOKUP({"a","b"},{"a";"b";"c"},{1;2;3})` is stored as the Defined Name *XLook*, then `=SUM(XLook)` returns the incorrect 1, since it resolves to operating over the first element only within *lookup_value*, i.e. is equivalent to `=SUM(XLOOKUP("a",{"a";"b";"c"},{1;2;3}))`. This seems buggy behaviour to me. – Jos Woolley Jun 24 '23 at 06:27
  • 2
    As per your example, other functions do not seem to suffer this inconsistency. `=SUM(FILTER({1;2;3},{1;1;0}))`, within the worksheet, correctly returns 3, though so does `=SUM(MyFilter)`, where *MyFilter* is the Defined Name `=FILTER({1;2;3},{1;1;0})`. – Jos Woolley Jun 24 '23 at 06:30
  • 1
    Well found! @JosWoolley – P.b Jun 24 '23 at 09:29
  • 3
    I did some additional test based on your example @JosWoolley, using `XMATCH` works. Wrapping `Xlook` with `TOCOL` or `TOROW` doesn't work, but using. `=BYROW(Xlook, LAMBDA(x, SUM(x)))`works. As you said, it seems to be a bug. – David Leal Jun 25 '23 at 04:19

0 Answers0