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


"cindyb" wrote:

> 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.

[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




"cindyb" <[email protected]> wrote in message
news:[email protected]...
> 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.

[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).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cindyb" <[email protected]> wrote in message
news:[email protected]...
> 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.

[[email protected]]

"cindyb" wrote:
> 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!!

"cindyb" wrote:

> 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.