building off previous column formula

[Castor]

Hey there,

Got one that has stumped me! below is a small sample of a table i am working with. I have already created a column formula to return 'Adult' or 'Child' based on age.

What I need is for that formula to also take into account 'Both' since for some payers they do not differentiate by adult/child.

Next, What I need to do is in the next column, return a given number of visits allowed based on the adult/child/both column and payer. There is a table that stipulates the visits allowed by age and payer. I tried if/or/and formulas but could not get it to work myself for the different payers/visit limits.

Last, once we have those two columns solved, I wanted to add another column to return whether authorization is required at this time based on payer, age, and whether or not the number of visits (specific to each payer) are approaching the visit limits.

Please let me know if this is not clear or if anything else can be provided. Help is greatly appreciated! or if other more efficient ways are thought of, I'm all ears. Looking to learn.

Book2
	B	C	D	E	F	G	H	I	J	K	L
1	COMPANY	AGE	PAYER	Adult or Child	# Visits Allowed?	Needs Authorization?
2	PAT 1	15	PAYER 1	Child				PLAN NAME	ADULT	CHILD	BOTH COMBINED
3	PAT 2	14	PAYER 2	Child				PAYER 1			24
4	PAT 3	22	PAYER 3	Adult				PAYER 2	8	16
5	PAT 4	45	PAYER 4	Adult				PAYER 3			24
6	PAT 5	11	PAYER 5	Child				PAYER 4	16	24
7	PAT 6	9	PAYER 6	Child				PAYER 5	8	16
8	PAT 7	9	PAYER 7	Child				PAYER 6	8	16
9	PAT 8	67	PAYER 4	Adult				PAYER 7			24
10	PAT 9	16	PAYER 5	Child				PAYER 8			20
11	PAT 10	29	PAYER 6	Adult
12	PAT 11	55	PAYER 7	Adult
13	PAT 12	12	PAYER 8	Child
14	PAT 13	14	PAYER 8	Child
15	PAT 14	44	PAYER 1	Adult
16	PAT 15	8	PAYER 2	Child
17	PAT 16	72	PAYER 3	Adult
18	PAT 17	16	PAYER 8	Child
Sheet2

Cell Formulas
Range	Formula
E2:E18	E2	=IF(OR(C2>=18),"Adult",)&IF(OR(C2<18),"Child",)

 

[Castor]

Ok, how about

=IF(COUNTIFS(D$2:D2,D2,E$2:E2,E2)>=F2-3,"Needs Authorization","No Authorization needed at this time")

This is excellent, we are getting so close but not quite there. So I left out a column in my first table that gave a count of visits that already occurred. We want to return the "Needs..." or "No Auth..." based on whether the number of visits that have already occurred is within 3 visits of the limits. Below has the column for count.


See below

Book2
	B	C	D	E	F	G	H	I	J	K	L
1	COMPANY	AGE	PAYER	Adult or Child	# Visits Allowed?	Visits so far	Needs Authorization?	PLAN NAME	ADULT	CHILD	BOTH COMBINED
2	PAT 1	15	PAYER 1	Child	24	8		PAYER 1			24
3	PAT 2	14	PAYER 2	Child	16	1		PAYER 2	8	16
4	PAT 3	22	PAYER 3	Adult	24	4		PAYER 3			24
5	PAT 4	45	PAYER 4	Adult	16	15		PAYER 4	16	24
6	PAT 5	11	PAYER 5	Child	16	4		PAYER 5	8	16
7	PAT 6	9	PAYER 6	Child	16	3		PAYER 6	8	16
8	PAT 7	9	PAYER 7	Child	24	7		PAYER 7			24
9	PAT 8	67	PAYER 4	Adult	16	9		PAYER 8			20
10	PAT 9	16	PAYER 5	Child	16	5		PAYER 9			UNLIMITED
11	PAT 10	29	PAYER 6	Adult	8	3		PAYER 10			UNLIMITED
12	PAT 11	55	PAYER 7	Adult	24	2		PAYER 11			UNLIMITED
13	PAT 12	12	PAYER 8	Child	20	2
14	PAT 13	14	PAYER 8	Child	20	3
15	PAT 14	44	PAYER 1	Adult	24	3
16	PAT 15	8	PAYER 2	Child	16	4
17	PAT 16	72	PAYER 3	Adult	24	6
18	PAT 17	16	PAYER 8	Child	20	7
19	PAT 18	17	PAYER 9	Child	UNLIMITED	8
20	PAT 19	18	PAYER 10	Adult	UNLIMITED	9
21	PAT 20	19	PAYER 11	Adult	UNLIMITED	10
Sheet2

Cell Formulas
Range	Formula
E2:E21	E2	=IF(OR(C2>=18),"Adult",)&IF(OR(C2<18),"Child",)

 

[Fluff]

So I left out a column in my first table that gave a count of visits that already occurred.

Pretty major thing to "forget".
Can you post the data complete with the updated formulae.

 

[Castor]

Pretty major thing to "forget".
Can you post the data complete with the updated formulae.

My apologies, it was an afterthought. See below, let me know this is all you need.

Book2
	B	C	D	E	F	G	H	I	J	K	L
1	COMPANY	AGE	PAYER	Adult or Child	# Visits Allowed?	Visits so far	Needs Authorization?	PLAN NAME	ADULT	CHILD	BOTH
2	PAT 1	15	PAYER 1	Both	24	8		PAYER 1			24
3	PAT 2	14	PAYER 2	Child	16	1		PAYER 2	8	16
4	PAT 3	22	PAYER 3	Both	24	4		PAYER 3			24
5	PAT 4	45	PAYER 4	Adult	16	15		PAYER 4	16	24
6	PAT 5	11	PAYER 5	Child	16	4		PAYER 5	8	16
7	PAT 6	9	PAYER 6	Child	16	3		PAYER 6	8	16
8	PAT 7	9	PAYER 7	Both	24	7		PAYER 7			24
9	PAT 8	67	PAYER 4	Adult	16	9		PAYER 8			20
10	PAT 9	16	PAYER 5	Child	16	5		PAYER 9			UNLIMITED
11	PAT 10	29	PAYER 6	Adult	8	3		PAYER 10			UNLIMITED
12	PAT 11	55	PAYER 7	Both	24	2		PAYER 11			UNLIMITED
13	PAT 12	12	PAYER 8	Both	20	2
14	PAT 13	14	PAYER 8	Both	20	3
15	PAT 14	44	PAYER 1	Both	24	3
16	PAT 15	8	PAYER 2	Child	16	4
17	PAT 16	72	PAYER 3	Both	24	6
18	PAT 17	16	PAYER 8	Both	20	7
19	PAT 18	17	PAYER 9	Both	UNLIMITED	8
20	PAT 19	18	PAYER 10	Both	UNLIMITED	9
21	PAT 20	19	PAYER 11	Both	UNLIMITED	10
Sheet2

Cell Formulas
Range	Formula
E2:E21	E2	=IF(COUNT(INDEX($J$2:$L$12,MATCH(D2,$I$2:$I$12,0),{1,2}))=0,"Both",IF(C2>=18,"Adult","Child"))
F2:F21	F2	=INDEX($J$2:$L$12,MATCH(D2,$I$2:$I$12,0),MATCH(E2&"*",$J$1:$L$1,0))

 

[Fluff]

Thanks for that, but on closer inspection the number of visits makes no sense. How can Payer 1 go from 8 visits to 3, or Payer 4 from 15 to 9 amongst others.

 

[Castor]

Thanks for that, but on closer inspection the number of visits makes no sense. How can Payer 1 go from 8 visits to 3, or Payer 4 from 15 to 9 amongst others.

Think of it as 'pat'= patient, and "payer" = insurance. So Pat 1 and Pat 14 both have the same payer with the same visit limits, but Pat 1 has been to the doctor 8 times, and Pat 14 has been to the doctor 3 times. They're both limited to 24 visits before authorization is needed. So for both, it should be 'No Auth needed at this time'.

Does that help?

 

[Fluff]

Ok, how about

=IF(OR(G2<N(F2)-3,F2="Unlimited"),"No Authorization needed at this time","Needs Authorization")

 

[Castor]

Ok, how about

=IF(OR(G2<N(F2)-3,F2="Unlimited"),"No Authorization needed at this time","Needs Authorization")

Thank you so much. It worked like a charm. I even added a third condition for 'exceeds auth limits' using your suggestion as a base.

Book2
	B	C	D	E	F	G	H	I	J	K	L	M
1	COMPANY	AGE	PAYER	Adult or Child	# Visits Allowed?	Visits so far	Needs Authorization?		PLAN NAME	ADULT	CHILD	BOTH
2	PAT 1	15	PAYER 1	Both	24	8	No Authorization needed at this time		PAYER 1			24
3	PAT 2	14	PAYER 2	Child	16	1	No Authorization needed at this time		PAYER 2	8	16
4	PAT 3	22	PAYER 3	Both	24	4	No Authorization needed at this time		PAYER 3			24
5	PAT 4	45	PAYER 4	Adult	16	15	Needs Authorization		PAYER 4	16	24
6	PAT 5	11	PAYER 5	Child	16	4	No Authorization needed at this time		PAYER 5	8	16
7	PAT 6	9	PAYER 6	Child	16	3	No Authorization needed at this time		PAYER 6	8	16
8	PAT 7	9	PAYER 7	Both	24	7	No Authorization needed at this time		PAYER 7			24
9	PAT 8	67	PAYER 4	Adult	16	9	No Authorization needed at this time		PAYER 8			20
10	PAT 9	16	PAYER 5	Child	16	5	No Authorization needed at this time		PAYER 9			UNLIMITED
11	PAT 10	29	PAYER 6	Adult	8	3	No Authorization needed at this time		PAYER 10			UNLIMITED
12	PAT 11	55	PAYER 7	Both	24	25	Exceeded Authorization Limit		PAYER 11			UNLIMITED
13	PAT 12	12	PAYER 8	Both	20	2	No Authorization needed at this time
14	PAT 13	14	PAYER 8	Both	20	3	No Authorization needed at this time
15	PAT 14	44	PAYER 1	Both	24	3	No Authorization needed at this time
16	PAT 15	8	PAYER 2	Child	16	18	Exceeded Authorization Limit
17	PAT 16	72	PAYER 3	Both	24	6	No Authorization needed at this time
18	PAT 17	16	PAYER 8	Both	20	7	No Authorization needed at this time
19	PAT 18	17	PAYER 9	Both	UNLIMITED	8	No Authorization needed at this time
20	PAT 19	18	PAYER 10	Both	UNLIMITED	9	No Authorization needed at this time
21	PAT 20	19	PAYER 11	Both	UNLIMITED	10	No Authorization needed at this time
Sheet2

Cell Formulas
Range	Formula
E2:E21	E2	=IF(COUNT(INDEX($K$2:$M$12,MATCH(D2,$J$2:$J$12,0),{1,2}))=0,"Both",IF(C2>=18,"Adult","Child"))
F2:F21	F2	=INDEX($K$2:$M$12,MATCH(D2,$J$2:$J$12,0),MATCH(E2&"*",$K$1:$M$1,0))
H2:H21	H2	=IF(OR(G2<N(F2)-3,F2="Unlimited"),"No Authorization needed at this time",IF(OR(G2>N(F2)),"Exceeded Authorization Limit","Needs Authorization"))

 

[Fluff]

You're welcome & thanks for the feedback.