Numbers can coexist in Excel cells with text strings. What may surprise you is that the contents of such cells can be treated and used as *numbers*. It means that they can be used as ordinary numbers in mathematical calculations, functions and formulas. This happens when you apply some kinds of **custom formatting **for cells.

Here is such **example** of cell formatting and some confusing results of using it in formulas involving text strings.

I've used the following

**custom format**for cells in column

**A**:

**[Blue][>0.9]#.### "In";[Red][<=0.9]#.### "Out";General**

so that values higher than **0.9** are coloured **in blue** and have added (*in the same cell*) custom text "**In**" indicating that they meet my requirements. The values equal or below **0.9** are coloured **in red** and have added custom text "**Out**" indicating that they do not meet my requirements.

Cells in column **C** have the General or Number format and contain the underlying formulas (shown in column **D**), resulting in displayed values. As you can see, the formulas ignore the added text strings in column **A** cells and work fine, on numbers only.

However, the results shown in cells **C6** and **C7** look somewhat strange. What's wrong with them? Have a look at the value shown in the *Formula Bar*; it displays the full length of the number displayed in the cell **A4**. The same format applies to all cells in Column **A**, so cell **C6** shows also the full length of the value, i.e. **17**. And cell C7 displays the right five digits of the number displayed in cell A7 (where only first three digits are displayed).

__Conclusion__:*custom formatting of cells in Excel can be very useful in most cases, but not always. Caution is advised, when applying complex custom formatting, because unreliable / unintended results can be obtained unexpectedly in some cases.*

## No comments:

## Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.