# Unexpected calculation results

Occasional Contributor

# Unexpected calculation results

Hi, I'm struggling to understand why my formula is returning unexpected numeric values.

=B9-(B9*B10/100)

Evaluation shows:

="1,5"-("1,5"*20/100)

But further steps inn, suddenly it is interpreted to:

="1,5"-(886340/100)

The values in B9 are calculated from the formula

=XLOOKUP(B4;J4:L4;J5:L5;0)

Values in B10 is from a list.

Any tips? Does the xlookup formula results have a special format, overriding the cell formats?

11 Replies

# Re: Unexpected calculation results

Apparently the values in J5:L5 are text values, since the value of B9 (which comes from J5:L5) has quotes around the value.

Since you want to perform a calculation with the value of B9, it should be a number.

So you should ensure that the values in J5:L5 are numbers, not text values.

# Re: Unexpected calculation results

Strange, ="1,5"*20 shall be evaluated to 30. But in any case agree with @Hans Vogelaar , better to return number in B9. As variant to wrap XLOOKUP by VALUE.

# Re: Unexpected calculation results

Thank's for Your reply. I figured that cell formatining was a issue. The problem is that I can't change this. I have configured all cells.

# Re: Unexpected calculation results

Why can't you change it?

# Re: Unexpected calculation results

All cells are formated as numbers. I have even ensured that the underlying data (lists, and datafields that is relevant for the Xlookup functions) are formated as numbers.

Still the final calculation step seems to read (B9) it as text format.

# Re: Unexpected calculation results

Assuming that J5:L5 contain values, not formulas, try the following:

• Select an empty cell.
• Copy it.
• Select J5:L5.
• Right-click in the selection and select Paste Special... from the context menu.
• Click OK.

Does that change anything?

If not, could you attach a small sample workbook (without sensitive/proprietary data) that demonstrates the problem?

# Re: Unexpected calculation results

Actually J5:L5 containe formulas such as =XLOOKUP(B7;' TAC ISMS'!A25:A48;' TAC ISMS'!E25:E48;0;1)
The results from this formula is shown correctly in appropriate cells J5:L5, but it seems that the resulting data, when it's then used for calculations i B9 somehow is enterpreted as text.

Cells J5:L5 are formated as numbers with one decimal.

# Re: Unexpected calculation results

Could you attach a sample workbook? There are so many levels of formulas that it's hard to know what's going on.

# Re: Unexpected calculation results

Because of your regional settings Excel evaluates "1,5" to 01.05.2021 which equals to 44317 - hence the result 886340.

# Re: Unexpected calculation results

Yes, thank you that seems to be the problem. Any use of data refering to B9 generates the same fault. So somehow the data in B9 is treated as date format. The cell format is numbers, so any idea to which regional settings to try? and why suddenly an issue in this sheet?

# Re: Unexpected calculation results

Check the regional setting for date and time in Windows.