Formula for Multiple Variables

dannie

New Member
Joined
Dec 29, 2014
Messages
34
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>
AgeCompCont
405000013741
401000027481
4015000041222
4021000057711
415000014833
4110000029665
4115000044498
4121000062297
425000016041
4210000032082
4215000048123
4221000067372

<tbody>
</tbody>

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

-D
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.

ABCDEFGHIJ
1AgeCompContAgeActual CompThreshold CompApplicable ContAdjusted Cont1Adjusted Cont2
24050,00013,7414160,00050,00014,83317,80017,800
34010,00027,481
440150,00041,222
540210,00057,711
6
74150,00014,833
841100,00029,665
941150,00044,498
1041210,00062,297
11
124250,00016,041
1342100,00032,082
1442150,00048,123
1542210,00067,372

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

Worksheet Formulas
CellFormula
I2=F2/G2*H2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
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>
 
Upvote 0
DRSteele - you are a genius! thank you so much. this is exactly what i was looking! Thank you!
-D
 
Upvote 0
Genius? Hardly (but I'll take the compliment!) You're welcome.
 
Upvote 0

Forum statistics

Threads
1,214,285
Messages
6,118,674
Members
448,845
Latest member
MrEbzz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top