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

 

[Gerald Higgins]

Hi. I don't know about anyone else, but this is very unclear to me.
Is the data in cols J/K/L somehow derived from the data in cols B/C/D/E ?
If so, how ?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

For the 1st two, how about

++Fluff.xlsm
	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	Both	24			PLAN NAME	ADULT	CHILD	BOTH COMBINED
3	PAT 2	14	PAYER 2	Child	16			PAYER 1			24
4	PAT 3	22	PAYER 3	Both	24			PAYER 2	8	16
5	PAT 4	45	PAYER 4	Adult	16			PAYER 3			24
6	PAT 5	11	PAYER 5	Child	16			PAYER 4	16	24
7	PAT 6	9	PAYER 6	Child	16			PAYER 5	8	16
8	PAT 7	9	PAYER 7	Both	24			PAYER 6	8	16
9	PAT 8	67	PAYER 4	Adult	16			PAYER 7			24
10	PAT 9	16	PAYER 5	Child	16			PAYER 8			20
11	PAT 10	29	PAYER 6	Adult	8
12	PAT 11	55	PAYER 7	Both	24
13	PAT 12	12	PAYER 8	Both	20
14	PAT 13	14	PAYER 8	Both	20
15	PAT 14	44	PAYER 1	Both	24
16	PAT 15	8	PAYER 2	Child	16
17	PAT 16	72	PAYER 3	Both	24
18	PAT 17	16	PAYER 8	Both	20
Main

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

 

[Castor]

Hi. I don't know about anyone else, but this is very unclear to me.
Is the data in cols J/K/L somehow derived from the data in cols B/C/D/E ?
If so, how ?

No - J,K,L are just static values I included there to inform the user of what the visit limits are by age/payer. The only column at the moment that is derived is the column "Adult or Child", and that is derived with a formula based on the "Age" column.

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done! Thank you for the tip - btw it is 2016, Windows.

 

[Fluff]

Done!

Thanks

 

[Castor]

For the 1st two, how about

++Fluff.xlsm
	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	Both	24			PLAN NAME	ADULT	CHILD	BOTH COMBINED
3	PAT 2	14	PAYER 2	Child	16			PAYER 1			24
4	PAT 3	22	PAYER 3	Both	24			PAYER 2	8	16
5	PAT 4	45	PAYER 4	Adult	16			PAYER 3			24
6	PAT 5	11	PAYER 5	Child	16			PAYER 4	16	24
7	PAT 6	9	PAYER 6	Child	16			PAYER 5	8	16
8	PAT 7	9	PAYER 7	Both	24			PAYER 6	8	16
9	PAT 8	67	PAYER 4	Adult	16			PAYER 7			24
10	PAT 9	16	PAYER 5	Child	16			PAYER 8			20
11	PAT 10	29	PAYER 6	Adult	8
12	PAT 11	55	PAYER 7	Both	24
13	PAT 12	12	PAYER 8	Both	20
14	PAT 13	14	PAYER 8	Both	20
15	PAT 14	44	PAYER 1	Both	24
16	PAT 15	8	PAYER 2	Child	16
17	PAT 16	72	PAYER 3	Both	24
18	PAT 17	16	PAYER 8	Both	20
Main

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

This portion is working like a charm! Thank you! Just need that last one.

 

[Fluff]

What are the criteria for the last column?

 

[Castor]

What are the criteria for the last column?

There will be another column with a count of visits so far. I would like to have it return either the value "Needs Authorization" or "No Authorization needed at this time" based on whether it comes with 3 or less units from the visit limit.

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

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

So if a limit is 8 visits, i'd like it to return "Needs Authorization" at >=5 visits and "No Authorization needed at this time" for <5 visits. Same goes with others:

16 visits, i'd like it to return "Needs Authorization" at >=13 visits and "No Authorization needed at this time" for <13.

So if we take Row 5 above, it should be "Needs Authorization" because the limit for adults is 16 and there have been 15 visits so far.

 

[Fluff]

Ok, how about

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