Using If and Large Formula

[manloonw]

Hi All,

Looking for a solution for the below:

New Column with either R or S. If Transaction for X, are less than 4 on 11/12/20 than all columns are "R", If more than four transaction on 11/12/20, use "R" on that transaction, all others use "S".

Can anyone help with this formula? I currently can only get the <=4 based on date.

IF(COUNTIFS(B:B,B3,C:C,C3)<=4,"R",0))

X/Y	Date	Time	Kinds	Ticket	Net Sales	Time zone	Type	Price
X	11/12/2020​	12:35	Adult	5​	250​	12​		50​
X	11/12/2020​	12:35	Others	2​	50​	12​		25​
X	11/12/2020​	21:30	Adult	10​	750​	21​		75​
X	11/12/2020​	10:45	Adult	2​	100​	10​		50​
X	11/12/2020​	12:40	Adult	5​	250​	12​		50​
X	11/12/2020​	12:40	Others	7​	175​	12​		25​
Y	11/12/2020​	22:15	Adult	4​	300​	22​		75​
Y	11/12/2020​	22:15	Others	3​	150​	22​		50​
Y	11/12/2020​	22:15	Adult	4​	300​	22​		75​

 

[Zot]

For us here we cannot see what column are there

Please use XL2BB to capture your worksheet. That way helpers here can see where you apply formula, what columns and rows.

 

[manloonw]

See if this helps:

A

B

C

D

E

F

G

G

I

J

K

L

M

N

O

2​

X/Y

Date

Time

Kinds

Ticket

Net Sales

Time zone

Type

Price

Release

Week

R/S

Lower Rate

O. Rate

Income

3​

X

11/12/2020​

12:35

Adult

5​

250​

12​

50​

Minor

wk1

s

0.4​

100​

4​

X

11/12/2020​

12:35

Others

2​

50​

12​

25​

Minor

wk1

s

0.4​

20​

5​

X

11/12/2020​

21:30

Adult

10​

750​

21​

75​

Minor

wk1

r

0.45​

337.5​

6​

X

11/12/2020​

10:45

Adult

2​

100​

10​

50​

Minor

wk1

s

0.4​

40​

7​

X

11/12/2020​

12:40

Adult

5​

250​

12​

50​

Minor

wk1

s

0.4​

100​

8​

X

11/12/2020​

12:40

Others

7​

175​

12​

25​

Minor

wk1

r

0.45​

78.75​

9​

Y

11/12/2020​

22:15

Adult

4​

300​

22​

75​

Minor

wk1

s

0.4​

120​

10​

Y

11/12/2020​

22:15

Others

3​

150​

22​

50​

Minor

wk1

r

0.45​

67.5​

11​

Y

11/12/2020​

22:15

Adult

4​

300​

22​

75​

Minor

wk1

s

0.4​

120​

 

[Zot]

Need further explanation on:
If Transaction for X, are less than 4 on 11/12/20 means on this sample Transaction X on 11/1/2020 = 6 ?
Then all columns are R. What columns are we looking at?
If more than four transaction on 11/12/20, use "R" on that transaction, all others use S. Again where is this S should be? Are you talking about column L? If so, then I cannot understand column L is like that for S and R. Are these expected result?

 

[manloonw]

Sorry about the lack of information.

Please ignore what's currently in Column L, I'm looking for a correct formula to go in this column.

X Relates to an entity. So for example, there are only 3 transactions on Y on the 11/12/20 therefore for Column L all values should = "R"

For X, as there are more than 4 transaction on the 11/12/20, I would like Column L to have a "R" based on the top 4 the highest values (Column F) = Row 5,9, 11 & (either 3 or 7, but not both)

can this be possible done in one formula ?