+name: why affects enclosing fn's result?

Programming
, ,
1

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

2

The formula =SUM(EffectiveScore) does not give the correct answer because it considers the entire column of EffectiveScore values in the Total table, including the header and other unrelated values. This results in an incorrect sum.

Adding a + before EffectiveScore (=SUM(+EffectiveScore)) tells Excel to only consider the values in the EffectiveScore column and ignore any other values. This ensures that only the relevant values are summed, resulting in the correct total score.

To get the correct answer without using the + sign, you can modify the formula to exclude the header and other unrelated values. Here’s the modified formula:

=SUM(FILTER(EffectiveScore, EffectiveScore<>0))

This formula uses the FILTER function to only consider the non-zero values in the EffectiveScore column, excluding the header and any other unrelated values. Applying SUM to this filtered range will give you the correct total score.