• 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

A

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

  • Chandoo.xlsx
    514.1 KB · Views: 4
aspatil said:
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
Click to expand...
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!
 
shrivallabha said:
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!
Click to expand...
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
 
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))))))
 
AliGW said:
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))))))
Click to expand...
I tried this , But it shows TRUE or FALSE. We need values for for perticular conditios
 
AliGW said:
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))))))
Click to expand...
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
 
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))))))
 
AliGW said:
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))))))
Click to expand...
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

  • Chandoo.xlsx
    514.6 KB · Views: 2
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. :(
 
AliGW said:
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. :(
Click to expand...
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
 
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. :)
 
You must log in or register to reply here.
Back
Top