1.01 to 99.01% .....

[emvella]

Hi,


I want an Excel page that when I write 1.01 in a cell it writes 99.01% in other cell, when I write 1.02 it writes 98.04% in other cell and so on...


Here is the full table that I need:


1.01..........99.01%
1.02..........98.04%
1.03..........97.09%
1.04..........96.15%
1.05..........95.24%
1.06..........94.34%
1.07..........93.46%
1.08..........92.59%
1.09..........91.74%
1.10..........90.91%
1.11..........90.09%
1.12..........89.29%
1.13..........88.50%
1.14..........87.72%
1.15..........86.96%
1.16..........86.21%
1.17..........85.47%
1.18..........84.75%
1.19..........84.03%
1.20..........83.33%
1.21..........82.64%
1.22..........81.97%
1.23..........81.30%
1.24..........80.65%
1.25..........80.00%
1.26..........79.37%
1.27..........78.74%
1.28..........78.13%
1.29..........77.52%
1.30..........76.92%
1.31..........76.34%
1.32..........75.76%
1.33..........75.19%
1.34..........74.63%
1.35..........74.07%
1.36..........73.53%
1.37..........72.99%
1.38..........72.46%
1.39..........71.94%
1.40..........71.43%
1.41..........70.92%
1.42..........70.42%
1.43..........69.93%
1.44..........69.44%
1.45..........68.97%
1.46..........68.49%
1.47..........68.03%
1.48..........67.57%
1.49..........67.11%
1.50..........66.67%
1.51..........66.23%
1.52..........65.79%
1.53..........65.36%
1.54..........64.94%
1.55..........64.52%
1.56..........64.10%
1.57..........63.69%
1.58..........63.29%
1.59..........62.89%
1.60..........62.50%
1.61..........62.11%
1.62..........61.73%
1.63..........61.35%
1.64..........60.98%
1.65..........60.61%
1.66..........60.24%
1.67..........59.88%
1.68..........59.52%
1.69..........59.17%
1.70..........58.82%
1.71..........58.48%
1.72..........58.14%
1.73..........57.80%
1.74..........57.47%
1.75..........57.14%
1.76..........56.82%
1.77..........56.50%
1.78..........56.18%
1.79..........55.87%
1.80..........55.56%
1.81..........55.25%
1.82..........54.95%
1.83..........54.64%
1.84..........54.35%
1.85..........54.05%
1.86..........53.76%
1.87..........53.48%
1.88..........53.19%
1.89..........52.91%
1.90..........52.63%
1.91..........52.36%
1.92..........52.08%
1.93..........51.81%
1.94..........51.55%
1.95..........51.28%
1.96..........51.02%
1.97..........50.76%
1.98..........50.51%
1.99..........50.25%
2.00..........50.00%


Thanks

 

[Joe4]

Set up a lookup table (like you are showing above), and use a VLOOKUP formula.
See: https://www.techonthenet.com/excel/formulas/vlookup.php

 

[Eric Golf]

Hi Emvella,

Assuming your first value is in A1 then in the cell you want the result type

=(1/A1)

... and format as percentage with two decimals.

Hope this helps,

Eric.

 

[Joe4]

Assuming your first value is in A1 then in the cell you want the result type

=(1/A1)

... and format as percentage with two decimals.

Good catch to see that is what the relationship is.
My only advice would be to use the ROUND function, to limit it so they do not get any unexpected results (if being used in calculations), i.e.

[COLOR=#333333]=ROUND(1/A1,4)[/COLOR]

 

[emvella]

Hi Guys,

Maybe I didn't make myself clear.

What I want is for example if:
I write 1.01 in cell A1, then in cell B1 comes automatically 99.01%
I write 1.02 in cell A2, then in cell B2 comes automatically 98.04%
I write 1.03 in cell A3, then in cell B3 comes automatically 97.09%
etc....

And also if I write for example 1.01 in any cell of column A it comes always 99.01% and the same for all other numbers.

 

[Joe4]

Maybe I didn't make myself clear.

Did you try putting that formula in B1? I think it will do what you want!
Except we may want to add a condition that it only shows something once something is entered in column A, i.e.
enter this in B1 and copy down for all rows:

[COLOR=#333333]=IF(A1<>"",ROUND(1/A1,4),"")[/COLOR]

If that does not work for you, please explain how/why it does it work.

 

[emvella]

Hi Joe4,

Wow, that is perfect!

I cannot understand how you did it, but most important thing is that what I wanted

Thanks very much.

 

[Joe4]

You are welcome. Glad we could help.

Eric Golf recognized that your number in column B is just the reciprocal of the amount in column A. So you don't need a lookup, just the reciprocal formula.
I just added the rounding part and the IF part to only show something in column B if an entry appears in column A.