Newbie mistake - Same simple formula, different results

[WilliamYlab]

Hey, I'm extremely new to the website and a true beginner at Excel.

I'm studying Excel and its more complex formulas and my way of studying it is to take complex formulas and decompose them step by step to see how the results are formed.

That said, I seem to be having some problem with something that should be extremely simple.

I have the following set:

	A	B	C	D
1	value being compared	11
2	value 1	11	=(B2:B3<=B1)	TRUE
3	value 2	11	=(B2:B3<=B1)	#ERROR

<tbody>
</tbody>

The example above is not wrong in any value. If I use the exact same formula in two different rows, the results vary.

* If I change the value of the formula in C3 to (B3:B4<B2) - following Excel's pattern - it doesn't show an error.
* If I copy and paste the formula from C2 to D2, it doesn't show an error.

I just wanted to confirm that this is something with Excel and reproducible to others.

The goal with this was to understand the usage of the "--" (double negative) to transform TRUE and FALSE values into 1 and 0.

 

[Caleeco]

Hello William

I have set up a copy of your spreadsheet on Excel 2013, both instances return TRUE. When changing the values in Column C, the results change between TRUE/FALSE as expected.

Which version of Excel are you using?

Caleeco

 

[Norie]

Where are the -- in your formulas?

 

[WilliamYlab]

Hey Caleeco thank you so much for testing that out. I'm currently using Excel 2016, Brazilian Portuguese version.

 

[WilliamYlab]

Where are the -- in your formulas?

Hi Norie, I didn't even got to place the -- in the second formula (C3) because of the #ERROR result that it was displaying. Anyway, adding the -- doesn't change the #ERROR message, but changes the D2 result to 1, as expected.

 

[Caleeco]

Hi William,

Are you able to re-create the same issue yourself on a NEW workbook? Or is it just on the one you are working on?

Caleeco