You need to be careful when working with codes/ unique ID’s that have the letter E between numbers. We discovered this while assisting a client on MRI Property Software (formerly MDA), but it will be applicable to any system that could result in the letter E being between numbers when you are creating a unique ID.
Table of contents
YouTube SUMIF not working with MDA extracts
Example of inadvertent creation of a scientific notation number
In MRI Property Software (formally MDA), expense accounts are often prefixed with an E, e.g. e100 might be municipal expenses. In order to create a unique lookup reference in MS Excel, the property ID (which is a number in MRI Property software) is often joined together with the expense code with a CONCATENATE or similar function. So for example 4e102 means property 4, expense account 102. But when you try and use this in a SUMIF or SUMIFS you could get incorrect results.
As shown below, in cell D8 we have created a SUMIFS that adds up all the amounts in column D that matches the code 4e102. It should give the answer 0, as the matching row 4 has an amount of 0, but it is giving an answer of 120 which is adding up all 3 rows. So it is treating 4e102 as the same as 40e101 and 400e100 which reflects completely different buildings and different expense accounts.
The reason Excel is confusing the E between numbers
The reason this is happening is because, while we are seeing
- property 4 expense account 102, versus
- property 40 expense account 101, versus
- property 400 expense account 100,
Excel is turning it into a very big number (e is used as scientific notation) so Excel is seeing
- 4 with 102 zeros after it in the first case, and
- 40 with 101 zeros after it in the second case, and
- 400 with 100 zeros after it in the third case.
The net effect is that all the ‘numbers’ are 4 with 102 zeros after it which means they are the same thing, which is why Excel includes them in the SUMIF result.
How to stop Excel ‘seeing’ scientific notation in Unique IDs
The easiest way is to make sure the end result is never a number, with an E afterwards and another number after the E. You can achieve this by including a delimiter between the 2 items. We tend to use a | or – but it could almost be anything (probably not a space if you can avoid it).
As shown below, when the unique ID is something like 4|E102, Excel treats it as text and then gets the correct calculation.
MRI Property Software (MDA) Consulting
If you are experiencing this type of problem, where Excel seems to be adding incorrectly, it could be a similar issue so be careful of E between numbers.
Other likely Excel ‘misunderstandings’ would include R on South African computers (R is the prefix for South African currency), so Excel might see R100 as 100 South African Rand instead of Recovery account 100.
Have a look at some of our consulting options including the work we have been doing with creating management accounts for MRI Property Software.
Related
MDA Property Manager Excel Consulting