Rank if on various Criterias Problem

[manloonw]

Hi all,

Been having a nightmare trying to figure out formula for the below, if any Einstein around can help would mean alot.

I have gathered certain data regarding Cinema Sales and I'm trying to calculate based whether each show shall be based on the higher rate (Regular) or lower rate (Special) as in Column O.

% Rates differ on variables: The Week, Major (Big Cinema)/Minor (Small Cinema). Also there's conditions on if the show's are regular or special showing:

- Shows during peak times 14,17,19,21 will be automatically classed as Regular
- Week 1 must have at least 4 showings per day or else all will be classed as regular

- Less than 4 Regular Shows per day, will change the highest Gross Revenue Special Show and charge it as Regular. Until there's 4 regular shows.

*The breakdown of the rates can be found on "CORE Tab in Column L:N"

I have got the formulas down, but I can figure out how I should do the last condition. I have tried using the Countif function as Rankif in column N and linking this to my If formula in Column O as <=4 Rank but the error occurs when I have to limit the shows to 4 Regular. Example in row 4:8 it has classed all as regular.

Please help.

 

[manloonw]

Example Cinema.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Circuit	Cinema	Release	Type	Date	Time	House/ Hall	Admission	Gross Revenue	Time zone	Release2	Week	Shows Per Day	Rank	R/S	Rate	Film Rental
4	John Doe Cinema Management Ltd	EB	TAJ	2D	11-Mar	11:15	House 1	9	579	11	Major	1	5	2	R	50%	289.5
5	John Doe Cinema Management Ltd	EB	TAJ	2D	11-Mar	13:10	House 2	0	0	13	Major	1	5	4	R	50%	0
6	John Doe Cinema Management Ltd	EB	TAJ	2D	11-Mar	15:20	House 1	15	1685	15	Major	1	5	1	R	50%	842.5
7	John Doe Cinema Management Ltd	EB	TAJ	2D	11-Mar	19:35	House 1	5	0	19	Major	1	5	5	R	50%	0
8	John Doe Cinema Management Ltd	EB	TAJ	2D	11-Mar	21:40	House 1	2	260	21	Major	1	5	3	R	50%	130
DATA

Cell Formulas
Range	Formula
L4:L8	L4	=IF(B4="","",VLOOKUP(E4,'C:\Users\206676612\OneDrive - NBCUniversal\My Documents\Desktop\[EMP - TAJ. EOM TEST.xlsm]CORE'!F:G,2,FALSE))
M4:M8	M4	=IF(B4="","",COUNTIFS(C:C,C4,B:B,B4,E:E,E4))
N4:N8	N4	=IF(B4="","",(COUNTIFS(B:B,B4,E:E,E4,I:I,">"&I4)+1+COUNTIFS($B$2:B4,B4,$E$2:E4,E4,$I$2:I4,I4)-1))
O4:O8	O4	=IF(L4<=$L$1,IF(OR(J4=14,J4=17,J4=19,J4=21),"R",IF(M4<=4,"R",IF(N42<=4,"R","S"))),IF(OR(J4=14,J4=17,J4=19,J4=21),"R","S"))
P4:P8	P4	=IF(L4="SNEAK",IFERROR(INDEX(CORE!$M$2:$N$16,MATCH(("SNEAK"),CORE!$L$2:$L$16,0),MATCH(K4,CORE!$M$1:$N$1,0)),""),IFERROR(INDEX(CORE!$M$2:$N$16,MATCH((L4&O4),CORE!$L$2:$L$16,0),MATCH(K4,CORE!$M$1:$N$1,0)),""))
Q4:Q8	Q4	=IF(B4="","",I4*P4)
J4:J8	J4	=IF(B4="","",HOUR(F4))

Named Ranges
Name	Refers To	Cells
Cinema	=Table2[Cinema]	M4:N8
date	=DATA!$E$3:$E$57	M4:N8
GR	=Table2[Gross Revenue]	N4:N8

 

[manloonw]

Anyone?