Very basic if formula query

[SKG]

Hi,

I have a column displaying Alexa rank of websites. In another column, I need to make following remarks based on Alexa rank.

If rank is below 20000, it should display value 2 as remark in next column
If rank is between 20000 & 40000, it should display value 1.5 as remark in next column
If rank is above 40000, it should display value 2 as remark in next column

I believe there is a pretty simple formula for this but since I am new to Excel, I was unable to figure it out inspite of trying several times.

It would be really helpful if any Excel expert out there tells me how to do this.

Thanks in advance.

 

[NARAYANK991]

Hi ,

I am summarising your conditions below ; please confirm that they are correct.

1. If the Alexa rank is below 20000 or above 40000 , then in the remark column put 2

2. Otherwise , put 1.5

Another way to summarise this would be :

1. If the Alexa rank is between 20000 and 40000 , then in the remark column put 1.5

2. Otherwise , put 2

The Excel formula would be as follows , assuming the data starts from row 2 , and the Alexa rank is in column R :

=IF(OR(R2 < 20000 , R2 > 40000) , 2 , 1.5

or

=IF(AND(R2 >= 20000 , R2 <= 40000) , 1.5 , 2)

Enter either of the above formulae in S2 , and copy down.

Narayan

 

[Harpreet Singh]

If it difficult for you, use easy method.
Please find attached sheet.

You have to create a criteria range then apply VLOOKUP function.

 

[Somendra Misra]

Hi,

Considering your value in A2 you can use below formula also:

=LOOKUP(A2,{0,20000,40001},{2,1.5,2})

Regards,

 

[SKG]

Thanks a lot everyone. It solved my query. Thanks a lot once again for your timely help.

 

[Vijay Dhangar]

Try this also....
without using helper coloumns....