Need to dynamically calculate based on returned value

[Sleeplol]

Hello Wizards,

I'll explain this the best I can
I'm developing a calculator in which the answer in D10 needs to be divided by a different number according to its value.

Example
If the answer is between 0-100 it would be divided by .5
101-200 divided by .501
201-300 divided by .502
etc

How would I go about setting these parameters?
(Would there be a VBA function?)

Thanks

 

[lrobbo314]

No need for VBA.

<style type="text/css">table.LEGO-table {font-size: 12px;border: 1px solid #CCC;font-family: Arial, Helvetica, sans-serif;border-collapse: collapse;}.LEGO-table td {padding: 4px;margin: 3px;border: 0.1px solid #000000;}.LEGO-table th {background: linear-gradient(0deg, rgba(9,121,22,1) 24%, rgba(51,51,51,1) 100%);color: #FFF;font-weight: bold;border-collapse: separate;border: 0.1px solid #000000;}td.blank {background-color: #ebebeb;text-align: center;}.LEGO-table tr:nth-child(even) {background-color: #d9d9d9;}</style><table class="LEGO-table"><caption>LROBBO HTML</caption><thead><tr class="LEGO-firstrow"><tr><td class="blank"></td><td class="blank">D</td><td class="blank">E</td></tr><tr><td class="blank">9</td><th>Value</th><th>Result</th></thead><tbody><tr><td class="blank">10</td><td>201</td><td>400.398406374502</td></tr></tbody></table></br></br><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00; color:#000000; "><tbody><tr><td><b>Formulas</b></td></tr><tr><td><table class="LEGO-table"><tr><td>E10</td><td>=D10/LOOKUP(D10,{0,101,201},{0.5,0.501,0.502})</td></tr></table></tbody></table>

 

[Sleeplol]

Of course, VLOOKUP!!! Hey thanks Irobbo314

Ultimately I'll be going up to the high ten thousands place, so a whole lot of those "value brackets"; is there a trick to inputting that amount of options?

 

[lrobbo314]

Make sure you use Lookup not Vlookup. Also, instead of the brackets you can pass in ranges. So, if on your sheet you make a lookup table, first column the whole numbers, second column the rates, then you can just use those columns instead of hard coding the values into the formula. So something like =D10/Lookup(D10,$P$1:$P$100,$Q$1:$Q$100)

 

[Sleeplol]

Thank you, this works perfectly!!!

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[Sleeplol]

Hello Irobbo314,

I have another condition for the same calculator I'm hoping you'll help with:

Currently D10's value determines the number it is divided by in a particular range P1-Q10000
However, I would like to make another range that D10 would also lookup depending on another cells value.

Basically, if the value of D9 is less than or equal to 11 then D10 lookup range P1-Q10000; if D9 is greater than 11 then D10 lookup range R1-S10000.