Formula for Multiple Variables

[dannie]

Hello,

Thank you in advance if you can help.

I am hoping to do just a formula, but if I have to do a VBA code attached to a button that is fine.

I have a group ages that have 2 sets of variables attached, below is part of the data I am using.

If I enter an age of 41 and Comp of $60,000, I need the formula to be "=(lower amount/comp amount)*actual comp" "=(14833/50000)*60000" - since 60000 is more than the first option and less than the second (according to chart below).

I can do the formula for any amount, but I was hoping to be able to input an age and a comp amount and have a formula figure out the cont amount.

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Age	Comp	Cont
40	50000	13741
40	10000	27481
40	150000	41222
40	210000	57711
41	50000	14833
41	100000	29665
41	150000	44498
41	210000	62297
42	50000	16041
42	100000	32082
42	150000	48123
42	210000	67372

<tbody>
</tbody>

I appreciate any help! Thanks all! If you need further clarification or details let me know!

-D

 

[DRSteele]

Perhaps this meets your needs. It all relies on multiplication of arrays to accomplish the look-ups.

The formula in I2 relies on helper columns G and H. The formula in J2 is a single-cell formula that reports identical results to I2 but is complex.

	A	B	C	D	E	F	G	H	I	J
1	Age	Comp	Cont		Age	Actual Comp	Threshold Comp	Applicable Cont	Adjusted Cont1	Adjusted Cont2
2	40	50,000	13,741		41	60,000	50,000	14,833	17,800	17,800
3	40	10,000	27,481
4	40	150,000	41,222
5	40	210,000	57,711
6
7	41	50,000	14,833
8	41	100,000	29,665
9	41	150,000	44,498
10	41	210,000	62,297
11
12	42	50,000	16,041
13	42	100,000	32,082
14	42	150,000	48,123
15	42	210,000	67,372

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula I2 =F2/G2*H2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G2 {=MAX((E2=A2:A15)*(B2:B15<=F2)*B2:B15)} H2 {=INDEX(C2:C15,MATCH(E2&G2,A2:A15&B2:B15,0))} J2 {=F2/MAX((E2=A2:A15)*(B2:B15<=F2)*B2:B15)*INDEX(C2:C15,MATCH(E2&MAX((E2=A2:A15)*(B2:B15<=F2)*B2:B15),A2:A15&B2:B15,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[dannie]

DRSteele - you are a genius! thank you so much. this is exactly what i was looking! Thank you!
-D

 

[DRSteele]

Genius? Hardly (but I'll take the compliment!) You're welcome.