Formula for Multiple Variables

dannie

New Member
Joined
Dec 29, 2014
Messages
30
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
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>
 

dannie

New Member
Joined
Dec 29, 2014
Messages
30
DRSteele - you are a genius! thank you so much. this is exactly what i was looking! Thank you!
-D
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
Genius? Hardly (but I'll take the compliment!) You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,573
Members
414,389
Latest member
MarkElla

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
Top