Formula does not work if it refers to a cell that contains a number that is the result of a formula

[SunValley]

I am using the following formula
=IF(B2=LARGE(B2:B22;1);"A";"B")

The formula works fine if the cells being referenced contain numbers, but if the numbers in those cells are the result of a formula then things don't work.

 

[kweaver]

Your issue is the cells in B2:B22 contain text as in TEXTbefore.
Try this:

=1*TEXTBEFORE(A2,".")

Assuming you KNOW that the characters before the period are all numbers.

 

[MARK858]

Is the below what you require (if you copy and paste with Ctrl + V the example using the

at the top of the XL2BB screenshot for the copy you'll probably find it will change the separators for you)?

Book1.xlsb
	A	B	C	D
1	Original name	Number before the first dot
2	8.0.png	8		A
3	8.0.png	8		A
4	5.0.png	5		B
5	3.0.png	3		A
6	2.0.png	2		B
7	1.0.png	1		A
Sheet3

Cell Formulas
Range	Formula
B2:B7	B2	=1*TEXTBEFORE(A2, ".")
D2:D7	D2	=IF(ISNUMBER(XMATCH(B2,LARGE(UNIQUE($B$2:$B$22),{1;3;5;7}))),"A","B")

 

[MARK858]

Is there any reason for this behavior?

Yes, your regional list separator is ; rather than , which mine and fluff's are. The list separator is normally a , in the USA and UK (where both myself and fluff are based).
What your setting is dependent on what country your Windows settings are for.

Comma or Semicolon in an Excel formula

Learn why Excel formulas use commas or semicolons as separators and how to change this setting in your Windows configuration.

www.excel-exercise.com

List separator

This article explains the function list separator used in Excel functions.

exceljet.net

 

[kweaver]

I don't have an issue w/it when I use the English separator of a comma vs a semi-colon. Maybe show your data using XL2BB.

 

[JoeMo]

Works fine for me when B2:B22 holds this formula:

=RANDBETWEEN(1,100)

 

[SunValley]

Works fine for me when B2:B22 holds this formula:

=RANDBETWEEN(1,100)

In cells B2:B22 I am using the formula =TEXTBEFORE(A2; ".")

 

[SunValley]

I don't have an issue w/it when I use the English separator of a comma vs a semi-colon. Maybe show your data using XL2BB.

I am using the formula =TEXTBEFORE(A2; ".") in B2:B22
But when using a simpler formula in B2:B22, such as adding two cells, then the formula =TEXTBEFORE(A2; ".") works normally

 

[SunValley]

Your issue is the cells in B2:B22 contain text as in TEXTbefore.
Try this:

=1*TEXTBEFORE(A2,".")

Assuming you KNOW that the characters before the period are all numbers.

Thank you very much, it worked perfectly
Thanks also to JoeMo for trying to help

 

[SunValley]

Sorry to reactivate the topic. I'm trying a variation of the formula =IF(B2=LARGE(B2:B22;1);"A";"B")
to check if the mentioned cell is also part of the first, second or third largest number =IF(B3=LARGE(B2:B22;{1,2,3});"A";"B")

However, Excel tries to correct the formula for something other than my purpose. When denying the correction, it is reported as an error.