IF(AND) function?

[cindyb]

Does Excel have the ability to calculate the following"

I have a calculated value in cell A51.

If that calculated value is in the range of numbers in the first column
below, I need a formula for Excel to return the value in the second column
below to cell A60.

0 - 500,000 250
500,001 -750,000 300
750,001 - 1,000,000 350
1,000,001 - up 400

Any help is greatly appreciated.

 

[Ron Coderre]

Try something like this:

With this:
________Col_A______Col_B
Row_1___0_________250
Row_2___500,001___300
Row_3___750,001___350
Row_4___1,000,001__400

And a value in A51
A60: =VLOOKUP(A51,A1:B4,2,1)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Peo Sjoblom]

No need for that, use lookup

=LOOKUP(A51,{0;500001;750001;1000001},{250;300;350;400})

if A51 can be blank you might want to use

=IF(A51="",""LOOKUP(A5,{0;500001;750001;1000001},{250;300;350;400}))

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Max]

One way, via VLOOKUP

Put in A60:
=IF(OR(A51="",A51<=0),"",VLOOKUP(A51,{0,250;500001,300;750001,350;1000001,40
0},2))

The above VLOOKUP carries a "standalone" table_array, so we don't need to
reference the table elsewhere. The IF error traps will ensure that a neat
looking blank: "", is returned (instead of ugly #N/As).

 

[joeu2004]

I have a calculated value in cell A51.
If that calculated value is in the range of numbers in the
first column below, I need a formula for Excel to return
the value in the second column below to cell A60.
0 - 500,000 250
500,001 -750,000 300
750,001 - 1,000,000 350
1,000,001 - up 400

Others have suggested a lookup function, which arguably
might be the best approach. If you still prefer an IF()
function, this particular example is not too bad:

=IF(A51 <= 500000, 250,
IF(A51 <= 750000, 300, IF(A51 <= 1000000, 350, 400)))

 

[cindyb]

Thanks so much for your help! It works!!