I have 3 tables:
RawScore: a table of raw scores for players each day
ScoreMod: a table of modifiers to each players’ daily raw score
Total: a table that calculates their total effective scores
I use the following function to calculate each player’s daily effective score (raw scores + modifiers):
=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)
When I use
=SUM(EffectiveScore) for Tom’s total score in the Total table, the result is incorrect, becoming 300. However, when I use
=SUM(+EffectiveScore) for Tom’s total score, 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