Calculating amount paid during a period based on age

[MSDemocrat]

I work for a child welfare agency and I am trying to create a formula that will calculate the total amount paid for a child in foster care where there are three rate categories based on age range.

The three rate categories are:
Age Rate
0-8 $22.83
9-15 $26.28
16-21 $28.72

My spreadsheet has 4 columns: Entry date, exit date, date of birth , Amount paid.

I would like the amount paid column to calculate the amount paid based on the number of days (exit date - discharge date) x the appropriate rate based on age on each day of service. So if the child spent two days in care and was 8 years old on both days the amount paid would equal $45.66 (22.83*2) but if the child turned 9 on the second day the total amount paid would be $49.11(22.83+26.28).

Can a formula be written for the amount paid column that would automatically calculate all of this?

[azumi]

I create new worksheet for calculation,pls check for the results

[FlameRetired]

Have you tried either VLOOKUP or LOOKUP functions? It works like this ...... with a lookup table in G2:H4 and this formula in cell C2 and filled down as below.

=LOOKUP(B2,$G$2:$H$4)*B2

Row\Col	A	B	C	D	E	F	G	H	I
1	Attained age	NumDays@Age
2	1	3	68.49				0	22.83
3	7	2	45.66				9	26.28
4	8	4	91.32				16	28.72
5	10	1	22.83
6	14	7	159.81
7	17	21	603.12

Does this help?

[FlameRetired]

Please disregard previous post. There are errors (up too late ). This adjustment should do it. Change the lookup table as below and use this formula.

=IFERROR(LOOKUP(A2,$G$2:$H$5)*B2,"Adult")

Row\Col	A	B	C	D	E	F	G	H
1	Attained age	NumDays@Age
2	1	3	68.49				0	22.83
3	7	2	45.66				9	26.28
4	8	4	91.32				16	28.72
5	10	1	26.28				22	Over Age
6	14	7	183.96
7	17	21	603.12
8	22	3	Adult
9	24	10	Adult
10	21	10	287.2

If you provide a sample Excel file (please no sensitive data) with "dummied data" that exemplifies layout, data type(s) that clearly shows us what you are working with and what you want results to be it would give us a clearer idea of what to suggest.