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]

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

 

[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.

 

[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.

 

[Fluff]

Try

=IF(B3>=LARGE(B2:B22;3);"A";"B")

 

[SunValley]

Try

=IF(B3>=LARGE(B2:B22;3);"A";"B")

It does not show the correct result.

The goal is to check if cell B2 is part of the first, second or third largest number in the range B2:B22.

Furthermore, I realize that in the previous formula where I mentioned =IF(B2=LARGE(B2:B22;1);"A";"B") , there was a need for the number of numbers mentioned in the formula to present a result.

For example, if I try =IF(B2=LARGE(B2:B22;5);"A";"B") it checks if cell B2 matches the fifth largest number in the range B2:B22. However, if in the range B2:B22 there are only 4 filled cells instead of 5, the formula returns #NUM!, when it should return B, since cell B2 is not part of the fifth largest number (although this fifth number does not exist in the range B2:B22.