May 31 2021 02:38 AM
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?
May 31 2021 03:54 AM - edited May 31 2021 06:49 AM
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.
May 31 2021 04:31 AM
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.
May 31 2021 04:57 AM
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.
May 31 2021 06:35 AM
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.
May 31 2021 06:51 AM
Assuming that J5:L5 contain values, not formulas, try the following:
Does that change anything?
If not, could you attach a small sample workbook (without sensitive/proprietary data) that demonstrates the problem?
May 31 2021 07:24 AM
May 31 2021 07:36 AM
Could you attach a sample workbook? There are so many levels of formulas that it's hard to know what's going on.
May 31 2021 07:55 AM
Because of your regional settings Excel evaluates "1,5" to 01.05.2021 which equals to 44317 - hence the result 886340.
May 31 2021 09:09 AM
May 31 2021 09:58 AM