## Excel doesn’t implicitly cast

It’s always interesting troubleshooting things that students run into when they work with Excel. For example, in Excel when is one not equal to one, or for that matter when is one less than one? If you use the `CODE(`

function, you’d find both cells contain the same ASCII value of 49.*cell_reference*)

If you’re wondering why the one on the left is less than the one on the right, the one on the right is a text value and the one on the left is a number. If you use the `TYPE(`

function, you’d find that a text field returns a 2 and a number (or date) cell returns a 1. All equality comparisons are identity comparisons in Microsoft Excel, which means they compare type and value. Inequality comparisons, compare type and return a value based on the type number and never compare value when the types differ. There are three other types: (1) A logical value is 4; (2) An error message is 16; and (3) An array is 64.*cell_reference*)

The answer is when one cell contains a number and the other cell contains a string (text value). That’s done here by putting an apostrophe before the number and right aligning the cell. You solve this by using the `VALUE(`

function to ensure that you’re comparing values not unknown data types because there’s no implicit casting resolution in Microsoft Excel and all comparisons are identity operations.*cell_reference*)

I haven’t checked Open Office or Numbers, but I’ll do that and update this posting with findings.

Thank you! Now I understand why

`SUMIF(Array1, "<1", Array2)`

doesn't add the value of an`Array2`

cell if the corres.`Array1`

cell contains`""`

returned by a formula. But I still don't get something. Type returns 1 for a completely empty cell. But`SUMIF`

as above still doesn't add an`Array2`

cell if the corres`Array1`

cell is empty. Using Excel 2010.Debbie12 Jan 13 at 7:48 pm

[…] Microsoft Excel SUMIF function […]

SUMIF null ranges13 Jan 13 at 5:15 pm

Debbie, I’ve tried to answer your question here. If it answers your question let me know and if not, let me know if you need more help.

maclochlainn13 Jan 13 at 5:16 pm

Have you any idea where MS has documented this ?

Thanks for your help

Pecoflyer23 Jul 13 at 10:07 am

I’ve not found it on their site. 🙁

maclochlainn26 Jul 13 at 2:20 pm

Thanks man, very helpfull 😉

Tiago4 Jan 14 at 6:05 am

Excellent! Thank you! This has solved a few problems I’ve struggled with for a few years.

Jim17 Mar 14 at 12:33 pm

Thank you for this excellent tip. Way easier than the cut/paste special multiply method I have used in the path.

David25 Mar 15 at 6:46 am