MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel doesn’t implicitly cast

with 7 comments

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(cell_reference) function, you’d find both cells contain the same ASCII value of 49.

ExcelCellComp

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(cell_reference) 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.

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(cell_reference) 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.

ExcelValueComp

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

Written by maclochlainn

November 10th, 2009 at 7:39 pm

7 Responses to 'Excel doesn’t implicitly cast'

Subscribe to comments with RSS or TrackBack to 'Excel doesn’t implicitly cast'.

  1. 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.

    Debbie

    12 Jan 13 at 7:48 pm

  2. […] Microsoft Excel SUMIF function […]

    SUMIF null ranges

    13 Jan 13 at 5:15 pm

  3. 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.

    maclochlainn

    13 Jan 13 at 5:16 pm

  4. Have you any idea where MS has documented this ?
    Thanks for your help

    Pecoflyer

    23 Jul 13 at 10:07 am

  5. I’ve not found it on their site. :-(

    maclochlainn

    26 Jul 13 at 2:20 pm

  6. Thanks man, very helpfull ;)

    Tiago

    4 Jan 14 at 6:05 am

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

    Jim

    17 Mar 14 at 12:33 pm

Leave a Reply