Help with IF / AND

[shahdelsol]

I am using this IF/AND formula and it works but in certain scenario doesn't work the way I want it.

=IF(AND(M41>=12,M41<=14.5,AD41>=40),M41*50,IF(AND(M41>=15,M41<=17.5,AD41>=55),(M41*50)+700,IF(AND(M41>=18,M41<=19.5,AD41>=65),(M41*50)+700+850,IF(AND(M41>=20,AD41>=75),(M41*100)+700+850+950,0))))

As you see there are 4 parts with IF/AND.
The problem is if AD41 on the second IF <55 but >39, and still M41>=15,M41<=17.5 true, I still want 14*50

If AD41 on the third IF <65 but >54 and still M41>=18,M41<=19.5 true, I still want (17*50)+700

If AD41 on the forth IF <75 but >64 and still M41>=20, I still want (19*50)+700+850

I hope this all makes sense. Is there a better way to handle this without adding more IF / ANDs? Thanks in advance. ​

​

 

[AlphaFrog]

Is this what you want?

IF(AND(M41>=12,M41<=14.5, AD41>=40, AD41<55),M41*50...

 

[shahdelsol]

No that wouldn't work. What I am trying to achieve, it might not be solved with IF. Technically all 4 IF should be independently from each other and adding up the results of all four IF + IF + IF + IF could be another solution but there is also one problem with that . What I want to do as soon as the first condition met, take the result then add up to 2nd condition if met and so on. But let's say 1st and 2nd conditions met but 3rd condition not met because AD41 not true. This formula produces 0 while I sill want the result of 1st and 2nd. Does this make sense?

Is this what you want?

IF(AND(M41>=12,M41<=14.5, AD41>=40, AD41<55),M41*50...

 

[Snakehips]

@shahdelsol

Any chance that this is what you are wanting?

=IF(M41>=20,IF(AD41>=75,(M41*100)+2500,IF(AND(AD41<75,AD41>64),2500,0)),0)+IF(AND(M41>=18,M41<=19.5),IF(AD41>=65,(M41*50)+1550,IF(AND(AD41<65,AD41>54),1550,0)),0)+IF(AND(M41>=15,M41<=17.5),IF(AD41>=55,(M41*50)+700,IF(AND(AD41<55,AD41>39),700,0)),0)+IF(AND(M41>=12,M41<=14.5,AD41>=40),M41*50,0)

Hope that helps.

 

[AlphaFrog]

But let's say 1st and 2nd conditions met but 3rd condition not met because AD41 not true. This formula produces 0 while I sill want the result of 1st and 2nd. Does this make sense?

I don't follow. The way you describe it, the third condition is irrelevant. Perhaps you should make a logic table.

 

[shahdelsol]

Thanks Snakeships but this didn't work. I think I will need to explain this first so you know what I am looking for.

@shahdelsol

Any chance that this is what you are wanting?

=IF(M41>=20,IF(AD41>=75,(M41*100)+2500,IF(AND(AD41<75,AD41>64),2500,0)),0)+IF(AND(M41>=18,M41<=19.5),IF(AD41>=65,(M41*50)+1550,IF(AND(AD41<65,AD41>54),1550,0)),0)+IF(AND(M41>=15,M41<=17.5),IF(AD41>=55,(M41*50)+700,IF(AND(AD41<55,AD41>39),700,0)),0)+IF(AND(M41>=12,M41<=14.5,AD41>=40),M41*50,0)

Hope that helps.

 

[shahdelsol]

I know this is a little confusing. All 4 IFs are independent so I think I need to create 4 different formulas in 4 different cells to make this easier and work smoother. I will explain how the first IF should be and then it would be easy to follow the pattern.

=IF(AND(M41>=12,AD41>=40),$600 + (M41-12*50),0

However I need to put cap on this part (M41-12 ) if the result of M41-12 is between .5 to 2.5 then I want (M41-12*50)

For example M41 = 13
Then 13-12 is 1 and 1*50= 50
If M41= 14.5 then 14.5-12 = 2.5*50=125 but if M41 is 15 or more then (M41-12*50) must equal 0 and essentially this formula the result this formula

IF(AND(M41>=12,AD41>=40),$600 + (M41-12*50),0 should be one of the followings:
a: 0 if is not met
b: 600 if met but

M41-12*50 = 0​

C: 625 if met but M41(12.5)-12
b: 650

if met but M41(13)-12​

c: 675

if met but M41(13.5)-12​

d: 700

if met but M41(14)-12​

e: 725

if met but M41(14.5)-12​

​

Once I figure this part then I will know how to make 3 more IFs for next scenario when M41 is 15 or bigger , 18 or bigger, 20 or bigger. How do I add the cap part to this IF formula or perhaps any other formula that make this work?



​

I don't follow. The way you describe it, the third condition is irrelevant. Perhaps you should make a logic table.

 

[AlphaFrog]

Try something like this...
=IF(AND(M41>=12,M41<=14.5,AD41>=40),$600 + ((M41-12)*50),0

 

[shahdelsol]

I already thought of this. It wouldn't work because if M41 is bigger than 14.5, I still want $600 + (M41-12)*50 but the result of M41-12 cannot be more than 2.5.
Think of a scenario that let's say M41=15 or 16 or 17 or any other number bigger than 14.5 then this formula would return 0 which is not what I want.

In a way it would be like this, + if((M41-12)>2.5, 2*50,M41-12*50

Try something like this...
=IF(AND(M41>=12,M41<=14.5,AD41>=40),$600 + ((M41-12)*50),0

 

[AlphaFrog]

This sets the cap at 2.5
=IF(AND(M41>=12,AD41>=40),$600 + (MIN(M41-12, 2.5)*50),0