Formula evaluation returns weird value
I created a formula in excel to get the iso.weeknumber in 2 numbers:
=JAAR(A2)&" "&ALS(LENGTE(ISO.WEEKNUMMER(A2)=1);"0"&ISO.WEEKNUMMER(A2);ISO.WEEKNUMMER(A2))
The result is this:
somehow, Excel thinks week 10 is length 1 in this formula. I checked, and only the length of weeknumber of the used date is 2 (see value in column N).
I then started formula evaluation and that is where something weird happens:
the result is false. but then this happens:
where does that 6 come from?
Solution
I created the formula in a new unformatted column, and then it works as it should:
=JAAR(A2)&" "&ALS(LENGTE(ISO.WEEKNUMMER(A2))<2;0&ISO.WEEKNUMMER(A2);ISO.WEEKNUMMER(A2))
- How to use SEQUENCE formula in Excel data validation
- Is there a better method than nested if/then to evaluate if cells in one set of columns has data, and then populate a different set of columns
- Filtering with multiple criteria output by another filter formula
- Why is SUMPRODUCT not calculating correctly?
- Groupby in excel using only formulas
- If cell contains item from range AND meets criteria in other column in range then return value
- Dynamically averaging data Per ID Per Time period
- How to reconcile the difference between two arrays with a unique tagging?
- Dynamically averaging data Per ID Per Time period Alternate Version
- Count consecutive excel cells where value is greater than 50 from left side only using excel formula
- Can you skip blank rows in a XMATCH lookup so #N/A is not returned?
- SUM with IF and AND
- transpose from one row with set a set of numbers to multiple columns with multiple rows downwards
- Using IFERROR with complex COUNTIFS
- find closed match value in matrix and return its row and column header in excel
- excel group column highlight matching values
- Excel Formula changes values after a day or so
- How to sort text values the same way as a web-based staircase generator
- VBA Code to filter a table and all its columns, whilst typing and ignoring accents
- Look up a value in a table based on two input values
- Search a column to find if string is contained in each cell of another column, return third value
- Excel to dynamically generate SQL script using formula
- Excel STOCKHISTORY for AMD
- How to get sum value from start time and end time?
- Why am I encountering this "#VALUE error" when using BYROW, LAMBDA and INDIRECT function together?
- Excel TEXTJOIN of non empty rows in a table
- #VALUE! Error Using Lambda And ByRow Helper Function in Excel (365)
- What Excel formula could I use to find the reference ID for a specific cell, given part of the text contained in that cell? like CTRL+F
- VBA or formula to repeat a list of items, and for each repeated list, add a location name from a separate locations list
- AUTO REPEAT DATA using formula EXCEL