79211388

Date: 2024-11-21 13:37:56
Score: 1
Natty:
Report link

You asked why, and there's no way to do this without a wall of text, so here's your wall of text: I'm not about to find out where, because quite frankly your method to get to the result is very convoluted, but somewhere in your network of filters and lookups, the cell containing -20, is being processed by a function that is either instructed to read it as text ("-20"), or the function itself is text-based in origin, and will therefore read and store any input as text. This all happens as an array in memory.

Secondly, you've stumbled upon the hidden, secret difference between an array and a range. Simply put, an array can only exist in memory to be remembered and manipulated, or transfered to a new array, and finally, by the function, one or any or all of these arrays can then be written to a range of cells. A range can't do any of that, it's more like a piece of paper you printed the result onto. If your function doesn't actually print data to cells, it's not a range, but an array. If you cannot see the range in your sheet, it doesn't exist.

RELEVANT: You (smartly) put EffectiveScore in Name Manager, so you could ezpz refer to it. It is essential to understand that EffectiveScore is calculated and stored as an array in memory. It doesn't exist as a range before you enter =EffectiveScore in a cell, and the function prints over 3 rows: 100, 80, 100.

Before you do that, this is what is stored in memory: 100, 100+"-20", 100. You may not see them as this, because you're thinking "I'm just doing math here, lol", but + and - are functions, just like SUM(). Unlike the SUM() function though, they are instructed to convert/read all inputs as numbers. When you enter =SUM(EffectiveScore), the range of EffectiveScore doesn't exist yet, so the SUM function uses the array stored in memory, which includes -20 stored as text. It skips that because "text isn't math, lmao", and writes 300 to your cell. When you add the +, you're instructing the SUM() function to read all inputs as numbers. Now it becomes 100, 100+-20, 100, which equals 280.

You can get the same effect by doing =SUM(NUMBERVALUE(EffectiveScore)).

It's the same thing that happens here: =1=1 will return TRUE, because both are 1, and both are numbers. ="1"=1 will return FALSE, even though both are 1, the first is a text string, and the other a number. =NUMBERVALUE("1")=1 will return TRUE, because NUMBERVALUE() converts it's text-string-intestines to a number. =0+"1"=1 will return TRUE because as previously exlained, the + function converts text "1" to a number: 1.

Reasons:
  • Blacklisted phrase (0.5): lmao
  • Long answer (-1):
  • No code block (0.5):
  • Low reputation (1):
Posted by: Blebbypants McFearson