Less than or equal to 3 variables

[fizzle]

Hi,

I am trying to write a formula in Excel 2k7 that basically states if an input is larger than "x" than this is the value but I have 3 variables.

For example:
Variables are 17000, 20100, 35500 (cells AH5, AI5 and AJ5, respectively)

I am taking the value from a cell (d27)

I would like a formula that will calculate if d27 is less than 17000 then "x2500" will be the output. If d27 is greater than 17000 but less than 20100 then "x4500" will be the output and lastly if d27 is greater than 20100 and less than 35500 then "x6500" will be the output.

Here is the latest formula i tried that didnt work.

=IF(D27<AH5,"x2500",if(not(d27>=AH5,"x4500"),if(d27>AI5,"x6500")

Any help would be greatly appreciated!!


-Fizzle

 

[MrKowz]

You can do this with a combination of IF and AND:

Try:

=IF(D27 < AH5,"x2500",IF(AND(D27>=AH5,D27 < AI5),"x4500",IF(AND(D27>=AI5,D27 < AJ5),"x6500","")))

 

[fizzle]

You can do this with a combination of IF and AND:

Try:

=IF(D27 < AH5,"x2500",IF(AND(D27>=AH5,D27 < AI5),"x4500",IF(AND(D27>=AI5,D27 < AJ5),"x6500","")))

Hi MrKowz,

I tried a combination of IF and AND but I wasn't able to get it to work out. Do you happen to have an example I can try?

Thanks!

 

[fizzle]

Hi MrKowz,

I tried a combination of IF and AND but I wasn't able to get it to work out. Do you happen to have an example I can try?

Thanks!

Sorry I see the formula now...I'm going to give it a try. Thanks!!!

 

[fizzle]

Sorry I see the formula now...I'm going to give it a try. Thanks!!!

Works perfectly!!! Thanks!!!!

 

[Ron Coderre]

Using your posted example

Where AH5:AJ6 contains these values:

17000     20100     35500
x2500     x4500     x6500

Try this formula:

=INDEX(AH6:AJ6,MATCH(1,FREQUENCY(D27,AH5:AJ5),0))

Is that something you can work witn?

 

[fizzle]

Hey Ron,

MrKowz's formula worked perfectly. I may use yours on a separate portion of the spreadsheet in another area where I think the indexing could work. Thanks!!!