mardi 18 décembre 2018

Need help fixing an IF(AND statement in excel

I am a beginner at using Excel and I've been trying to code a particular condition. The condition is as follows,

If A<75 and B<75 then do nothing
If A>75 and B<75 then A-75
If A<75 and B>75 then B-75
If A>75 and B>75 then (A-75)+(B-75)

Here, A and B are numerical values in two different cells. So I wrote a formula and it works when the IF statements are not nested. However when I combine the formula it gives me an error. The formula I wrote is as follows.

=IF(AND(S180<75,V180<75),0,"-",IF(AND(S180>75,V180<75),S180-75,"-",IF(AND(S180>75,V180<75),S180-75,"-",IF(AND(S180>75,V180>75),(S180-75)+(V180-75),"-"))))

The formulae that work are as follows,

=IF(AND(S180<75,V180<75),0,"-") 
=IF(AND(S180>75,V180<75),S180-75,"-") 
=IF(AND(S180>75,V180<75),S180-75,"-") 
=IF(AND(S180>75,V180>75),(S180-75)+(V180-75),"B") 

I want the conditions to all be applied in a single cell so that I can switch the resulting value into standard values from another table using the =switch function. I am using Excel Online. Any help fixing my formula is welcome.

Thanks in Advance!

Aucun commentaire:

Enregistrer un commentaire