Unexpected calculation results

[1Harald3055]

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?

[#2]

@1Harald3055 

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.

[#3]

@1Harald3055 

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.

[#4]

@Hans Vogelaar 

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.

[#5]

@1Harald3055 

Why can't you change it?

[#6]

@Hans Vogelaar 

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. 

[#7]

@1Harald3055 

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.
• Select Add.
• Click OK.

Does that change anything?

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

[#8]

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.

[#9]

@1Harald3055 

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

[#10]

@1Harald3055 

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

[#11]

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?

[#12]

Check the regional setting for date and time in Windows.