Auto populate

[Oscar]

Hi,

I'm working on a calculator for mortgage payments. On my calculator I
created a column with interest rate 1.000% - 11.000% and next to it I have a
column that automaticaly updates a ratio percentage I use for my calculator.

Column K Column O
1.000% 28.02%
1.010% 28.05%
1.020% -
-
-
-
11.000%

I have entered several formulas to get my values on Column O. When ever an
interest rate on column K equals to 31.00% on column O I have it highlight in
green. Since this column in long (Interest rate all the way down to H1004) I
want to enter a formula in cell B44 that will automatically update the exact
interest rate on column K that equals to 31.00% in column O instead of having
to scroll down the spreadsheet to find the Interest rate that equals to 31%.
Is this possible?

Thanks

 

[~L]

You could highlight column O, then use CTRL+F to search for 31% (match entire
cell contents).

By formula:
=index($K$1:$K$5000,match(31%,$O$1:$O$5000,0))

 

[Oscar]

Thanls for ypur response ~L. I used the formula, but I get an error "#NA". I
would like to know if instead of only looking for 31%, can the formula give
me the list of Rates that would equal anywhere between 31.00%: 31.44%?

Thanks

 

[~L]

The #N/A means it couldn't find 31% within the specified range. There could
be a number of reasons for this. For example, the number could be 31%
because the cell formatting specifies 0 decimal places (and it is 30.5% or
31.49% or something in between). Another reason could be, the number is
stored as text.

See if this suits your needs:

Array enter using CTRL+SHIFT+ENTER:

=index($K$1:$K$5000,match(1,($O$1:$O$5000>=31%)*($O$1:$O$5000<=31.44%)*,0))

My interpretation of your description was x>=31% x<=31.44%, if you meant to
exclude those min and max values, remove the = signs. You might also
consider replacing the 31% and 31.44% with references to cells containing
those values, since it will be easier to change the formula.

 

[Oscar]

I entered the formula exactly how you advised, but I get an error stating
that the formula contains an error. I doubled checked my cell range. Is there
something missing on the formula?

Thanks

 

[Glenn]

I'm guessing the last "*" was a mistake.

I entered the formula exactly how you advised, but I get an error stating
that the formula contains an error. I doubled checked my cell range. Is there
something missing on the formula?

Thanks

 

[~L]

Oops! That's correct. Extra *.

I'm guessing the last "*" was a mistake.

 

[Oscar]

Thanks again, it finaly worked. Only thing is that I only get one interest
rate instead of the list of available interest rates that would be in the
range of 31%:31.46%. What do I need to do in order to see all avaialbe
interest rates under that range?

Thanks

 

[Oscar]

I wanted to ask one final question. Since my list gives me a range of
possible interest rates I can use, what else can I enter to my existing
formula so that it gives me the minimum value in one cell and the maximum
value in another cell from the list of possible interest rates? The current
formula only gives me one value.

Thanks again

 

[Oscar]

Can anyone please help on my last question?

The #N/A means it couldn't find 31% within the specified range. There could
be a number of reasons for this. For example, the number could be 31%
because the cell formatting specifies 0 decimal places (and it is 30.5% or
31.49% or something in between). Another reason could be, the number is
stored as text.

See if this suits your needs:

Array enter using CTRL+SHIFT+ENTER:

=index($K$1:$K$5000,match(1,($O$1:$O$5000>=31%)*($O$1:$O$5000<=31.44%)*,0))

My interpretation of your description was x>=31% x<=31.44%, if you meant to
exclude those min and max values, remove the = signs. You might also
consider replacing the 31% and 31.44% with references to cells containing
those values, since it will be easier to change the formula.