Multiple Local Statements

N

NoCLUE

I am trying to get a value of XX in 1 box only for many situations..

Such as If A1 is less than 1.4 then cell is to read "90%

if A1 is > or = to 1.4 BUT less than 2.1 then read 77%

If A1 is > or = to 2.1 BUT less than 3.25 then read 70%

and so on

how do I do this
 
P

Puppet_Sock

NoCLUE said:
I am trying to get a value of XX in 1 box only for many situations..

Such as If A1 is less than 1.4 then cell is to read "90%

if A1 is > or = to 1.4 BUT less than 2.1 then read 77%

If A1 is > or = to 2.1 BUT less than 3.25 then read 70%

and so on

how do I do this
Click to expand...

Well, here's one way. (Coding at the terminal, may be typos.)

=if(a1<1.4,"90%",if(a1>=1.4 and a1<2.1,"77%", if(a1>=2.1 and
a1<3.25,"70%","and so on" ) ))

Another way is to write a VBA function in the code module
for the sheet, or possibly an added module for the workbook.

public function Grade(val as double) as string
{
if(val <1.4) then
return "90%"
elseif(val >=1.4 and val <2.1") then
}

and so on. Then in the cell you want you put

=Grade(a1)
Socks
 
J

JE McGimpsey

One way:

In another section of the worksheet (or on a second worksheet) enter
your corresponding values. For example:

J K
1 6.2 61%
2 4.83 65%
3 3.25 70%
4 2.1 77%
5 1.4 90%


Then you can use:

=INDEX(K:K,MATCH(A1,J:J,-1))
 
P

Patricia Shannon

A Select Case would be good here.

Puppet_Sock said:
Well, here's one way. (Coding at the terminal, may be typos.)

=if(a1<1.4,"90%",if(a1>=1.4 and a1<2.1,"77%", if(a1>=2.1 and
a1<3.25,"70%","and so on" ) ))

Another way is to write a VBA function in the code module
for the sheet, or possibly an added module for the workbook.

public function Grade(val as double) as string
{
if(val <1.4) then
return "90%"
elseif(val >=1.4 and val <2.1") then
}

and so on. Then in the cell you want you put

=Grade(a1)
Socks
Click to expand...
 
N

NoCLUE

Ok to be exact on this. It is for a interest rate swap feature that I
need.

goes like this

A1 <1.4% then cell is to read "90%

if A1 is > or = to 1.4% BUT less than 2.1% then read 77%

If A1 is > or = to 2.1% BUT less than 3.25% then read 72%

If A1 is > or = to 3.25% BUT less than 4..25% then read 70%

If A1 is > or = to 4.25% BUT less than 7.0% then read 67%

If A1 is > or = to 7.0% BUT less than 9.00% then read 64.25%

If A1 is > 9.% then read 50.5%

this is what It needs to say total.

thanks
 
P

Peo Sjoblom

Try

=LOOKUP(A1,{0;0.014;0.021;0.0325;0.0425;0.07;0.09},{0.9;0.77;0.72;0.7;0.67;0.6425;0.505})

or

=IF(A1="","",LOOKUP(A1,{0;0.014;0.021;0.0325;0.0425;0.07;0.09},{0.9;0.77;0.72;0.7;0.67;0.6425;0.505}))

if A1 can be blank at times

format as percentage


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
Top