Formula help please

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have the following entry fields:
A B C D
1 inv. obj % cash % Bond % stock
2 Growth 10% 30% 60%
3
4
Then I have made a table that contains criteria as follows:
A b c d
5 Inv. obj cash low range cash high range bond low
6 Growth 0% 3% 6%
7 Income 5% 12% 60%
8 Preservation 20% 60% 30%

Etc. And column e would be the bond high range, then
column f would be the stock low range, and column g would
be the stock high range.

I would like to create a formula in cell E2 that says the
following:
Lookup my entry in a2 in the table from column a6:a8.
Then determine if my cash percentage entry is either
within the range, or out of the range that is determined
by the table column b and c(for whichever inv. obj. that
is being looked up. If it is out of range, return "out of
range", if it is in range, return "OK".

Thanks alot.
Scott
 
Hi Scott
try the following in E2
=IF(AND(B2>=VLOOKUP(A2,$A$6:$D8,2,0),B2<=VLOOKUP(A2,$A$6:$D8,3,0)),"OK"
,"Out of range")

HTH
Frank
 
-----Original Message-----
Hi Scott
try the following in E2
=IF(AND(B2>=VLOOKUP(A2,$A$6:$D8,2,0),B2<=VLOOKUP (A2,$A$6:$D8,3,0)),"OK"
,"Out of range")

HTH
Frank



.
Hi Frank. Thank you for being willing to come to my
Click to expand...
rescue again. These formulas can be pretty overwhelming
sometime. I entered the formula that you gave me and am
getting a #n/a result. Any suggestions?
 
scott said:
rescue again. These formulas can be pretty overwhelming
sometime. I entered the formula that you gave me and am
getting a #n/a result. Any suggestions?
Click to expand...

Hi
looks like the MATCH function did not find a match. Possible reasons:
wrong lookup criteria, different text, etc. If you like, mail me your
spreadsheet and I'll look at it
Frank
 
-----Original Message-----
Hi Scott
try the following in E2
=IF(AND(B2>=VLOOKUP(A2,$A$6:$D8,2,0),B2<=VLOOKUP (A2,$A$6:$D8,3,0)),"OK"
,"Out of range")

HTH
Frank



.
Frank. Just wanted to let you know that I had made a
Click to expand...
small mistake in entering your formula. When I
highlighed the range cells I forgot to include column a,
so I had $b$6:$d8 instead of $a$6:$8. For some reason it
seemed logical for me to do it this way since I already
entered column a as the look up column, but I guess it
needs to be included here also.
So I made this adjustment and it worked!
Thanks again. Scott
Just curious, why do you only put one dollar sign in front
of d8, rather than $d$8?
 
scott said:
small mistake in entering your formula. When I
highlighed the range cells I forgot to include column a,
so I had $b$6:$d8 instead of $a$6:$8. For some reason it
seemed logical for me to do it this way since I already
entered column a as the look up column, but I guess it
needs to be included here also.
So I made this adjustment and it worked!
Thanks again. Scott
Just curious, why do you only put one dollar sign in front
of d8, rather than $d$8?
Click to expand...

quite simple. I just forgot it :-)
Frank
 
Back
Top