Need to know formula to sum up 3 largest rates depending on age factor

[BrittanyS]

Hello,

I am making rate calculator for clients to type in their ages and the ages of their dependents to know their insurance premium. Blue Cross has the rule that subscribers only pay for the first three dependents under the age of 21. (the largest 3). These ages and rates are only a sample. Could be a variety of ages...The spreadsheet will only have slots for 8 dependents. NA means no dependent.

A sum of the three largest rates but only through ages 0 to 20. The formula should then only consider the rate for the 11, 8 & 5 yr old.

21 $532.42
21 $532.42
5 $407.30
3 $407.30
8 $407.30
11 $407.30
NA $0.00
NA $0.00


Thank you,

Brittany

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want.

Book1
	A	B
1	Age	Rate
2	21	$532.42
3	21	$532.42
4	5	$407.30
5	3	$407.30
6	8	$407.30
7	11	$407.30
8	NA	$0.00
9	NA	$0.00
10
11	Top 3	$1,221.90
3 Largest < 21

Cell Formulas
Range	Formula
B11	=IFERROR(AGGREGATE(14,6,B2:B9/(A2:A9<21),1),0)+IFERROR(AGGREGATE(14,6,B2:B9/(A2:A9<21),2),0)+IFERROR(AGGREGATE(14,6,B2:B9/(A2:A9<21),3),0)

 

[BrittanyS]

The formula works absolutely wonderful! Thank you so much!

 

[Peter_SSs]

The formula works absolutely wonderful! Thank you so much!

You are welcome. Glad it helped.