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.

 

[MARK858]

I tried to copy and paste the formula you suggested, but for some reason it gives an error, Excel asks if I am trying to write a formula.

You need to change the , to ; as your separators

Any idea how I can do this?

I'm just going to work, I'll take a look when I get back in if @Fluff hasn't looked at it first

 

[Fluff]

Try

=IF(ISNUMBER(XMATCH(B2;LARGE($B$2:$B$22;{1;3;5;7})));"A";"B")

 

[SunValley]

Try

=IF(ISNUMBER(XMATCH(B2;LARGE($B$2:$B$22;{1;3;5;7})));"A";"B")

Try

=IF(ISNUMBER(XMATCH(B2;LARGE($B$2:$B$22;{1;3;5;7})));"A";"B")

I replicated the formulas but the result seems inaccurate.

Notice that in column B, the number 5 is the second largest number, so it is outside the {1,3,5,7} formula match, so it should return "B", because it doesn't match.

The same mismatch occurs with number 3, 2 and 1. Only number 8 gives the correct result, which is "A".

Pasta1.xlsm
	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		A
5	3.0.png	3		B
6	2.0.png	2		A
7	1.0.png	1		B
Sheet1

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

 

[Fluff]

The 5 is the 3rd largest number as you have two values of 8

 

[SunValley]

You need to change the , to ; as your separators

I made the change and it worked perfectly, now I just need to correct the mention of {1,3,5,7}.

I'm just going to work, I'll take a look when I get back in if @Fluff hasn't looked at it first

Thank you very much

 

[SunValley]

The 5 is the 3rd largest number as you have two values of 8

Makes sense, is there any way around this? Because in my workflow I need that although equal numbers represent the same order. That is, both 8 numbers in the list should represent the first largest number, so as not to affect the rest of the result.

 

[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")

 

[SunValley]

Is the below what you require (if you copy the example using the

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

I tried it in two ways, first I copied the formula directly into the example cell that you provided. Then I tried clicking the copy button of XL2BB.
Both ways failed, the first makes Excel ask me if I'm using a formula, the second makes the formulas not show any result.

So I copied the formulas manually and replaced " , " with " ; " .

=IF(ISNUMBER(XMATCH(B2;LARGE(UNIQUE($B$2:$B$22);{1;3;5;7})));"A";"B")

So the formula worked perfectly

Is there any reason for this behavior?

Thanks a lot for the help and patience from you and all the members

 

[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

Comma or Semicolon in Excel formula? Do you know you can change this setting? But it isn't an Excel option but a Windows option.

www.excel-exercise.com

List separator

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

exceljet.net

 

[SunValley]

Thank you very much (;