• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

if function with less than and greater than

aspatil

New Member
Dear all,
IF(CR6<35&CR6>30,(AD6-((AD6-AE6)/5)*(CR6-30)),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(CR6<30&CR6>27,(AC6-((AC6-AD6)/3)*(CR6-27))))))
I am unable to get desired results above second criteria
PLease help me out
 

Attachments

shrivallabha

Excel Ninja
Dear all,
IF(CR6<35&CR6>30,(AD6-((AD6-AE6)/5)*(CR6-30)),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(CR6<30&CR6>27,(AC6-((AC6-AD6)/3)*(CR6-27))))))
I am unable to get desired results above second criteria
PLease help me out
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
 

AliGW

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

Try this:

=IF(AND(CR6<35,CR6>30),(AD6-((AD6-AE6)/5)*(CR6-30)),IF(AND(CR6<40,CR6>35),(AE6-((AE6-AF6)/5)*(CR6-35)),IF(AND(CR6<27,CR6>25.97),(AA6-((AA6-AC6)/1.03)*(CR6-25.97)),IF(AND(CR6<30,CR6>27),(AC6-((AC6-AD6)/3)*(CR6-27))))))
 

aspatil

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

Try this:

=IF(AND(CR6<35,CR6>30),(AD6-((AD6-AE6)/5)*(CR6-30)),IF(AND(CR6<40,CR6>35),(AE6-((AE6-AF6)/5)*(CR6-35)),IF(AND(CR6<27,CR6>25.97),(AA6-((AA6-AC6)/1.03)*(CR6-25.97)),IF(AND(CR6<30,CR6>27),(AC6-((AC6-AD6)/3)*(CR6-27))))))
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(AND(CR6<35,CR6>30),(AD6-((AD6-AE6)/5)*(CR6-30)),IF(AND(CR6<40,CR6>35),(AE6-((AE6-AF6)/5)*(CR6-35)),IF(AND(CR6<27,CR6>25.97),(AA6-((AA6-AC6)/1.03)*(CR6-25.97)),IF(AND(CR6<30,CR6>27),(AC6-((AC6-AD6)/3)*(CR6-27))))))
IF(CR6<35,CR6>30),(AD6-((AD6-AE6)/5)*(CR6-30))
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(CR6<30,CR6>27),(AC6-((AC6-AD6)/3)*(CR6-27))))))
If formulate in this way in different cells i get desired results but in different cells
 

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):

=IF(AND(CR5<35,CR5>=30),(AD5-((AD5-AE5)/5)*(CR5-30)),IF(AND(CR5<40,CR5>=35),(AE5-((AE5-AF5)/5)*(CR5-35)),IF(AND(CR5<27,CR5>=25.97),(AA5-((AA5-AC5)/1.03)*(CR5-25.97)),IF(AND(CR5<30,CR5>=27),(AC5-((AC5-AD5)/3)*(CR5-27))))))
 

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):

=IF(AND(CR5<35,CR5>=30),(AD5-((AD5-AE5)/5)*(CR5-30)),IF(AND(CR5<40,CR5>=35),(AE5-((AE5-AF5)/5)*(CR5-35)),IF(AND(CR5<27,CR5>=25.97),(AA5-((AA5-AC5)/1.03)*(CR5-25.97)),IF(AND(CR5<30,CR5>=27),(AC5-((AC5-AD5)/3)*(CR5-27))))))
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<35,CR6>30),(AD6-((AD6-AE6)/5)*(CR6-30))
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(CR6<30,CR6>27),(AC6-((AC6-AD6)/3)*(CR6-27))
Thanks
 

Attachments

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
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. :)
 
Top