# if function with less than and greater than

#### aspatil

##### New Member
Dear all,
I am unable to get desired results above second criteria

#### Attachments

• 514.1 KB Views: 4

#### AliGW

##### Active Member
What are the desired results? Where is the formula to be found?

#### aspatil

##### New Member
What are the desired results? Where is the formula to be found?
hi,
Desired result for CP12 is 569.830, CP51 564.674 and CP85 565.952 as per attached sheet

#### AliGW

##### Active Member
Why do you believe that

should evaluate to 569.830???

#### shrivallabha

##### Excel Ninja
Dear all,
I am unable to get desired results above second criteria
You need to reconsider your formula approach from logic perspective as well. & is concatenation operator and what you probably want to do is to test AND condition which is not the same.
CR6<35&CR6>30 should be
AND(CR6<35,CR6>30)
So you need to be aware of a situation where the value is 35, which any of the conditions will not be able to catch. I'd suggest describing your formula requirements clearly so that something appropriate can be suggested!

#### aspatil

##### New Member
You need to reconsider your formula approach from logic perspective as well. & is concatenation operator and what you probably want to do is to test AND condition which is not the same.
CR6<35&CR6>30 should be
AND(CR6<35,CR6>30)
So you need to be aware of a situation where the value is 35, which any of the conditions will not be able to catch. I'd suggest describing your formula requirements clearly so that something appropriate can be suggested!
dear sir,
i have to calculate value with more condition in single cell. i.e. if CR6<35 but CR6>30 then go to for 1 condition and CR6<30 but >27.5 then go for 2 another calulation

#### aspatil

##### New Member
Why do you believe that

should evaluate to 569.830???
yes

#### AliGW

##### Active Member
Well it doesn't, so you'll need to explain why you think it should.

Try this:

#### aspatil

##### New Member
Well it doesn't, so you'll need to explain why you think it should.

Try this:

I tried this , But it shows TRUE or FALSE. We need values for for perticular conditios

#### aspatil

##### New Member
Well it doesn't, so you'll need to explain why you think it should.

Try this:

IF(CR6<40,CR6>35),(AE6-((AE6-AF6)/5)*(CR6-35)),
IF(CR6<27,CR6>25.97),(AA6-((AA6-AC6)/1.03)*(CR6-25.97)),
If formulate in this way in different cells i get desired results but in different cells

#### AliGW

##### Active Member
For each set of criteria, you need AND:

=IF(AND(CR6<35,CR6>30),value_if_true,IF(AND ...

#### aspatil

##### New Member
For each set of criteria, you need AND:

=IF(AND(CR6<35,CR6>30),value_if_true,IF(AND ...
also not working

#### AliGW

##### Active Member
Just saying that something is not working without explaining in what way is of little us to those trying to help.

Try this (my last attempt at the guessing game, since you won't answer my simple questions):

#### aspatil

##### New Member
Just saying that something is not working without explaining in what way is of little us to those trying to help.

Try this (my last attempt at the guessing game, since you won't answer my simple questions):

Hi,
with below four different conditions i need to find the value of CP5 to CP248,
yes I get values if calculated in 4 different columns but i am trying to get in one cloumn,
if there is another way to calculate please help me

IF(CR6<40,CR6>35),(AE6-((AE6-AF6)/5)*(CR6-35)),
IF(CR6<27,CR6>25.97),(AA6-((AA6-AC6)/1.03)*(CR6-25.97)),
Thanks

#### Attachments

• 514.6 KB Views: 2

#### AliGW

##### Active Member
We are going round in circles.

1. What should happen if CR6 is exactly 30, 35, 40, 27 or 25.97?
2. What about lower than 25.97 and higher than 40?

Your formula syntax is wrong and you don't appear to have even tried what I suggested.

#### aspatil

##### New Member
We are going round in circles.

1. What should happen if CR6 is exactly 30, 35, 40, 27 or 25.97?
2. What about lower than 25.97 and higher than 40?

Your formula syntax is wrong and you don't appear to have even tried what I suggested.
1. using <= 30, 40, 27, 25.97 for particular conditions ,
2. there is no need of below 25.97 and above 40

I tried , but it shows TRUE and FALSE and i Need Values in cell
Thanks

#### AliGW

##### Active Member
1. using <= 30, 40, 27, 25.97 for particular conditions ,
But you aren't. Your formula doesn't cater for this.

2. there is no need of below 25.97 and above 40
CR88 is lower than 25.97 ...

#### aspatil

##### New Member
But you aren't. Your formula doesn't cater for this.

CR88 is lower than 25.97 ...
Below 25.97 no need

#### AliGW

##### Active Member
Fine - then don't be surprised that the formula returns FALSE for all rows in CR that are lower than 25.97 - there are several.

I have reached the limits of my patience. Good luck, but I'm out.