How to use a reference function in place of an IF function?

[moniqua33]

I am trying to find out adoption rates by using a reference function instead of the IF functions that I used in O11. Is there a formula that would work for this? It needs to be copied down the column like my IF formula. Thank you!

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Rabies cost/shot	$ 25.00
2	immunization cost/shot	$ 18.00			Adoption Rates
3	food and body weight	80%			Age (mos)	Amount
4	cost food/pound dog	$ 0.86			5	$ 200.00
5	shot treat cost	$ 2.00			11	$ 160.00
6					12	$ 65.00
7
8
9	Adopted Dogs								Expense Calculations						Revenue
10	Name	Age (in months)	Weight (in pounds)	Sex	City	Rabies shot?	Number of Immunizations	CustomerID	Rabies shot cost	Immunization costs	Total cost shots	Food cost	Treat cost	Total costs	Adoption Cost	Profit
11	Ace	42	6	M	Columbus	TRUE	1	24	$ 25.00	$ 18.00	$ 43.00	$ 40.25	$ 2.00	$ 85.25	$ 65.00	$ 20.25
12	Yoda	162	13	M	Columbus	TRUE	3	28	$ 25.00	$ 54.00	$ 79.00	$ 148.26	$ 6.00	$ 233.26	$ 65.00	$ 168.26
13	Toby	171	16	M	Dublin	FALSE	1	23	$ -	$ 18.00	$ 18.00	$ 158.07	$ 2.00	$ 178.07	$ 65.00	$ 113.07
14	Fluffy	10	4	F	Columbus	TRUE	1	21	$ 25.00	$ 18.00	$ 43.00	$ 11.35	$ 2.00	$ 56.35	$ 160.00	$ (103.65)
15	Daisy	5	2	F	Worthington	TRUE	2	11	$ 25.00	$ 36.00	$ 61.00	$ 5.68	$ 4.00	$ 70.68	$ 200.00	$ (129.32)
16	Corky	3	3	F	Dublin	FALSE	1	43	$ -	$ 18.00	$ 18.00	$ 4.64	$ 2.00	$ 24.64	$ 200.00	$ (175.36)
17	Scruffy	14	5	F	Worthington	FALSE	2	45	$ -	$ 36.00	$ 36.00	$ 15.48	$ 4.00	$ 55.48	$ 65.00	$ (9.52)
18	Lady	10	3	F	Worthington	FALSE	2	2	$ -	$ 36.00	$ 36.00	$ 10.66	$ 4.00	$ 50.66	$ 160.00	$ (109.34)
19	Bailey	174	7	F	Worthington	FALSE	2	42	$ -	$ 36.00	$ 36.00	$ 154.46	$ 4.00	$ 194.46	$ 65.00	$ 129.46
20	Marley	3	2	F	Columbus	FALSE	1	26	$ -	$ 18.00	$ 18.00	$ 3.96	$ 2.00	$ 23.96	$ 200.00	$ (176.04)
21	Bear	66	16	M	Columbus	FALSE	1	22	$ -	$ 18.00	$ 18.00	$ 67.77	$ 2.00	$ 87.77	$ 65.00	$ 22.77
22	Bruno	7	5	M	Columbus	FALSE	3	13	$ -	$ 54.00	$ 54.00	$ 9.46	$ 6.00	$ 69.46	$ 160.00	$ (90.54)
23	Alex	42	17	F	Columbus	FALSE	3	47	$ -	$ 54.00	$ 54.00	$ 47.82	$ 6.00	$ 107.82	$ 65.00	$ 42.82
24	Copper	11	5	M	Columbus	FALSE	3	48	$ -	$ 54.00	$ 54.00	$ 12.90	$ 6.00	$ 72.90	$ 160.00	$ (87.10)
25	Lucky	78	6	F	Columbus	FALSE	1	14	$ -	$ 18.00	$ 18.00	$ 71.21	$ 2.00	$ 91.21	$ 65.00	$ 26.21
26	Alfie	2	1	F	Dublin	TRUE	0	3	$ 25.00	$ -	$ 25.00	$ 2.41	$ -	$ 27.41	$ 200.00	$ (172.59)
27	Rocky	6	3	M	Columbus	FALSE	2	10	$ -	$ 36.00	$ 36.00	$ 7.22	$ 4.00	$ 47.22	$ 160.00	$ (112.78)
28	Allegra	8	3	M	Hilliard	FALSE	0	30	$ -	$ -	$ -	$ 8.94	$ -	$ 8.94	$ 160.00	$ (151.06)
29	Chance	2	3	M	Columbus	TRUE	0	31	$ 25.00	$ -	$ 25.00	$ 3.78	$ -	$ 28.78	$ 200.00	$ (171.22)
30	Milo	88	8	M	Hilliard	FALSE	3	32	$ -	$ 54.00	$ 54.00	$ 81.18	$ 6.00	$ 141.18	$ 65.00	$ 76.18
31	Alvin	28	15	M	Columbus	TRUE	1	33	$ 25.00	$ 18.00	$ 43.00	$ 34.40	$ 2.00	$ 79.40	$ 65.00	$ 14.40
32	Amadaous	6	3	M	Hilliard	TRUE	0	34	$ 25.00	$ -	$ 25.00	$ 7.22	$ -	$ 32.22	$ 160.00	$ (127.78)
33	Amber	12	5	F	Columbus	TRUE	0	35	$ 25.00	$ -	$ 25.00	$ 13.76	$ -	$ 38.76	$ 65.00	$ (26.24)
34	Teddy	8	5	M	Columbus	TRUE	2	36	$ 25.00	$ 36.00	$ 61.00	$ 10.32	$ 4.00	$ 75.32	$ 160.00	$ (84.68)
35	Ameila	173	11	F	Columbus	TRUE	0	37	$ 25.00	$ -	$ 25.00	$ 156.35	$ -	$ 181.35	$ 65.00	$ 116.35
36	Champ	30	5	M	Columbus	FALSE	3	38	$ -	$ 54.00	$ 54.00	$ 29.24	$ 6.00	$ 89.24	$ 65.00	$ 24.24
37	Amy	11	3	F	Columbus	TRUE	3	39	$ 25.00	$ 54.00	$ 79.00	$ 11.52	$ 6.00	$ 96.52	$ 160.00	$ (63.48)
38	Molly	10	4	F	Columbus	TRUE	3	40	$ 25.00	$ 54.00	$ 79.00	$ 11.35	$ 6.00	$ 96.35	$ 160.00	$ (63.65)
39	Prince	2	1	M	Gahanna	FALSE	0	41	$ -	$ -	$ -	$ 2.41	$ -	$ 2.41	$ 200.00	$ (197.59)
40	Bella	66	6	F	Columbus	TRUE	3	25	$ 25.00	$ 54.00	$ 79.00	$ 60.89	$ 6.00	$ 145.89	$ 65.00	$ 80.89
41	Andy	12	13	M	Columbus	FALSE	2	44	$ -	$ 36.00	$ 36.00	$ 19.26	$ 4.00	$ 59.26	$ 65.00	$ (5.74)
42	Angelica	4	2	F	Worthington	TRUE	1	6	$ 25.00	$ 18.00	$ 43.00	$ 4.82	$ 2.00	$ 49.82	$ 200.00	$ (150.18)
43	Lola	112	12	F	Columbus	FALSE	3	7	$ -	$ 54.00	$ 54.00	$ 104.58	$ 6.00	$ 164.58	$ 65.00	$ 99.58
44	Charlie	41	15	M	Columbus	TRUE	2	9	$ 25.00	$ 36.00	$ 61.00	$ 45.58	$ 4.00	$ 110.58	$ 65.00	$ 45.58
45	Laddie	4	3	M	Dublin	TRUE	1	48	$ 25.00	$ 18.00	$ 43.00	$ 5.50	$ 2.00	$ 50.50	$ 200.00	$ (149.50)
46	Apollo	1	1	M	Columbus	TRUE	0	17	$ 25.00	$ -	$ 25.00	$ 1.55	$ -	$ 26.55	$ 200.00	$ (173.45)
47	Lanie	4	3	F	Columbus	FALSE	0	5	$ -	$ -	$ -	$ 5.50	$ -	$ 5.50	$ 200.00	$ (194.50)
48	Sport	90	15	M	Worthington	TRUE	0	16	$ 25.00	$ -	$ 25.00	$ 87.72	$ -	$ 112.72	$ 65.00	$ 47.72
49	Jamie	1	1	M	Hilliard	FALSE	0	18	$ -	$ -	$ -	$ 1.55	$ -	$ 1.55	$ 200.00	$ (198.45)
50	Rio	9	4	M	Columbus	TRUE	3	37	$ 25.00	$ 54.00	$ 79.00	$ 10.49	$ 6.00	$ 95.49	$ 160.00	$ (64.51)
51	Beau	10	4	F	Hilliard	FALSE	0	17	$ -	$ -	$ -	$ 11.35	$ -	$ 11.35	$ 160.00	$ (148.65)
52	Coco	1	2	F	Worthington	FALSE	0	19	$ -	$ -	$ -	$ 2.24	$ -	$ 2.24	$ 200.00	$ (197.76)
53	Scout	11	4	M	Columbus	TRUE	3	29	$ 25.00	$ 54.00	$ 79.00	$ 12.21	$ 6.00	$ 97.21	$ 160.00	$ (62.79)
54	Sugar	3	2	F	Columbus	TRUE	0	15	$ 25.00	$ -	$ 25.00	$ 3.96	$ -	$ 28.96	$ 200.00	$ (171.04)
55	Ariel	1	2	F	Dublin	FALSE	0	4	$ -	$ -	$ -	$ 2.24	$ -	$ 2.24	$ 200.00	$ (197.76)
56	Zoey	7	3	M	Dublin	TRUE	2	41	$ 25.00	$ 36.00	$ 61.00	$ 8.08	$ 4.00	$ 73.08	$ 160.00	$ (86.92)
57	Rusty	4	1	M	Columbus	TRUE	0	5	$ 25.00	$ -	$ 25.00	$ 4.13	$ -	$ 29.13	$ 200.00	$ (170.87)
58	Snow	8	4	M	Columbus	FALSE	3	12	$ -	$ 54.00	$ 54.00	$ 9.63	$ 6.00	$ 69.63	$ 160.00	$ (90.37)
59	Indy	12	8	F	Dublin	TRUE	3	32	$ 25.00	$ 54.00	$ 79.00	$ 15.82	$ 6.00	$ 100.82	$ 65.00	$ 35.82
60	Shadow	8	4	M	Columbus	TRUE	3	8	$ 25.00	$ 54.00	$ 79.00	$ 9.63	$ 6.00	$ 94.63	$ 160.00	$ (65.37)
61
62	Number of dogs needing Rabies shots	26													TOTAL	$ (3,116.19)
63
64	City	Average Profit														$ (37,394.26)
65	Columbus	$ (43.89)
66	Dublin	$ (90.46)
67	Gahanna	$ (197.59)
68	Hilliard	$ (109.95)
69	Worthington	$ (59.85)
70	TOTALS	$ (501.74)
71
Adopted Dogs

Cell Formulas
Range	Formula
I11:I60	I11	=IF(F11,$B$1,0)
J11:J60	J11	=PRODUCT(G11,$B$2)
K11:K60	K11	=SUM(I11,J11)
L11:L60	L11	=SUM(C11*$B$3,B11)*$B$4
M11:M60	M11	=PRODUCT(G11,$B$5)
N11:N60	N11	=SUM(K11,L11,M11)
O11:O60	O11	=IF(B11<=$E$4,$F$4,IF(B11<=$E$5,$F$5,IF(B11>=$E$6,$F$6)))
P11:P60	P11	=N11-O11
B62	B62	=COUNTIF(F11:F60,TRUE)
P62	P62	=SUM(P11:P60)
P64	P64	=AVERAGE(P62*12)
B65:B69	B65	=AVERAGEIFS(P11:P60,E11:E60, A65)
B70	B70	=SUM(B65:B69)

 

[jtakw]

Hi,

Does this work for you.
Change your E4:E6 table values as shown, use O11 formula copied down:

Adoption Rates
Age (mos)	Amount
0	200
6	160
12	65

Book3.xlsx
	O
10	Adoption Cost
11	65
12	65
13	65
14	160
15	200
16	200
17	65
18	160
19	65
20	200
21	65
22	160
23	65
24	160
25	65
26	200
27	160
28	160
29	200
30	65
31	65
32	160
33	65
34	160
35	65
36	65
37	160
38	160
39	200
40	65
41	65
42	200
43	65
44	65
45	200
46	200
47	200
48	65
49	200
50	160
51	160
52	200
53	160
54	200
55	200
56	160
57	200
58	160
59	65
60	160
Sheet1002

Cell Formulas
Range	Formula
O11:O60	O11	=LOOKUP(B11,E$4:E$6,F$4:F$6)

 

[Peter_SSs]

Since the other values in that lookup table are the upper value of the range, another option would be just to change the yellow cell so that it is too, and then use the formula shown in column O

22 02 12.xlsm
	B			E	F									O
1	25
2	18			Adoption Rates
3	0.8			Age (mos)	Amount
4	0.86			5	200
5	2			11	160
6				2000	65
7
8
9														Revenue
10	Age (in months)			City	Rabies shot?									Adoption Cost
11	42			Columbus	TRUE									65
12	162			Columbus	TRUE									65
13	171			Dublin	FALSE									65
14	10			Columbus	TRUE									160
15	5			Worthington	TRUE									200
16	3			Dublin	FALSE									200
17	14			Worthington	FALSE									65
18	10			Worthington	FALSE									160
19	174			Worthington	FALSE									65
20	3			Columbus	FALSE									200
21	66			Columbus	FALSE									65
22	7			Columbus	FALSE									160
23	42			Columbus	FALSE									65
24	11			Columbus	FALSE									160
25	78			Columbus	FALSE									65
26	2			Dublin	TRUE									200
27	6			Columbus	FALSE									160
28	8			Hilliard	FALSE									160
29	2			Columbus	TRUE									200
Lookup

Cell Formulas
Range	Formula
O11:O29	O11	=XLOOKUP(B11,E$4:E$6,F$4:F$6,,1)

 

[moniqua33]

Hi,

Does this work for you.
Change your E4:E6 table values as shown, use O11 formula copied down:

Adoption Rates
Age (mos)	Amount
0	200
6	160
12	65

Book3.xlsx
	O
10	Adoption Cost
11	65
12	65
13	65
14	160
15	200
16	200
17	65
18	160
19	65
20	200
21	65
22	160
23	65
24	160
25	65
26	200
27	160
28	160
29	200
30	65
31	65
32	160
33	65
34	160
35	65
36	65
37	160
38	160
39	200
40	65
41	65
42	200
43	65
44	65
45	200
46	200
47	200
48	65
49	200
50	160
51	160
52	200
53	160
54	200
55	200
56	160
57	200
58	160
59	65
60	160
Sheet1002

Cell Formulas
Range	Formula
O11:O60	O11	=LOOKUP(B11,E$4:E$6,F$4:F$6)

Hi there,

This code gives me NA errors for certain values.

PerkyPupDogShelterAML.xlsm
	O	P	Q
7
8
9	Revenue
10	Adoption Cost	Profit
11	$ 65.00	$ 20.25
12	$ 65.00	$ 168.26
13	$ 65.00	$ 113.07
14	$ 200.00	$ (143.65)
15	$ 200.00	$ (129.32)
16	#N/A	#N/A
17	$ 65.00	$ (9.52)
18	$ 200.00	$ (149.34)
19	$ 65.00	$ 129.46
20	#N/A	#N/A
21	$ 65.00	$ 22.77
22	$ 200.00	$ (130.54)
23	$ 65.00	$ 42.82
24	$ 160.00	$ (87.10)
25	$ 65.00	$ 26.21
26	#N/A	#N/A
27	$ 200.00	$ (152.78)
28	$ 200.00	$ (191.06)
29	#N/A	#N/A
30	$ 65.00	$ 76.18
31	$ 65.00	$ 14.40
32	$ 200.00	$ (167.78)
33	$ 65.00	$ (26.24)
34	$ 200.00	$ (124.68)
35	$ 65.00	$ 116.35
36	$ 65.00	$ 24.24
37	$ 160.00	$ (63.48)
38	$ 200.00	$ (103.65)
39	#N/A	#N/A
40	$ 65.00	$ 80.89
41	$ 65.00	$ (5.74)
42	#N/A	#N/A
43	$ 65.00	$ 99.58
44	$ 65.00	$ 45.58
45	#N/A	#N/A
46	#N/A	#N/A
47	#N/A	#N/A
48	$ 65.00	$ 47.72
49	#N/A	#N/A
50	$ 200.00	$ (104.51)
51	$ 200.00	$ (188.65)
52	#N/A	#N/A
53	$ 160.00	$ (62.79)
54	#N/A	#N/A
55	#N/A	#N/A
56	$ 200.00	$ (126.92)
57	#N/A	#N/A
58	$ 200.00	$ (130.37)
59	$ 65.00	$ 35.82
60	$ 200.00	$ (105.37)
Adopted Dogs

Cell Formulas
Range	Formula
O11:O60	O11	=LOOKUP(B11,E$4:E$6,F$4:F$6)
P11:P60	P11	=N11-O11

 

[moniqua33]

Thank you, it worked!

 

[jtakw]

You're welcome, thanks for the feedback.