Single or double

[James Clear]

Hi team,

I need urgnet help tp dervie whether single or double basis amount and premium

For example = let us say amount is 18000 and it has 2 premiums ( 252 & 504 ) then against 504 it should come as DOUBLE and against 252 it should come as SINGLE in the applicant column

Please help me basis excel version 365 ( Maybe by Let function )

Amt	Premium	Applicant
18,000	504	Double
18,000	252	Single
20,000	280	Single
20,000	560	Double

 

[SunnyAlv]

Try this, idk for ur logic, but i just know the logic is : if data Bigger = Double and Smaller = Single

*If there’s any unclear information please let me know

=LET(A,FILTER($H$2:$H$5,$G$2:$G$5=G2),B,INDEX($H$2:$H2,MATCH(G2,$G$2:$G2,1)),IF(B=MAX(A),"Double","Single"))

 

[Scott Huish]

This seems to work too.

Book1
	G	H	I
1	Amt	Premium	Applicant
2	18,000	504	Double
3	18,000	252	Single
4	20,000	280	Single
5	20,000	560	Double
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=IF(AND(COUNTIF(G:G,G2)>1,H2=MAXIFS($H$2:H5,$G$2:G5,G2)),"Double","Single")

 

[James Clear]

This seems to work too.

Book1
	G	H	I
1	Amt	Premium	Applicant
2	18,000	504	Double
3	18,000	252	Single
4	20,000	280	Single
5	20,000	560	Double
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=IF(AND(COUNTIF(G:G,G2)>1,H2=MAXIFS($H$2:H5,$G$2:G5,G2)),"Double","Single")

What about this one??

 

[Scott Huish]

The reason I didn't do it that way is that it doesn't work if there isn't a matching pair.

In this case, it seems like it should say Single for 18,000 as there is only 1 item.

Amt	Premium	Applicant
18,000​	504​	Double
20,000​	280​	Single
20,000​	560​	Double