Formula Help with multipe IF and ANDs and an ABS Formula- Excel 2010

[jenex11]

I'm trying to creat a formula that states. If tank 1 maximum capacity is less than 500,000 and the difference in inventory is greater than the absolute value of 10% or 20,000 or if the tanks maximum capacity is between 500,000 and 1,000,000 and the difference in inventory is greater than the absoulte value of 20% or 100,000 or if the maximum tank capacity is greater than 1,000,000 or the difference in inventory is greater than the absolute value of 30% or 300,000 then Y OR N.I hope my question is clear. I'm wondering if I need to simplfy the scenario. I figured out the absolute value portion of the formula but I'm not really sure how to combine that with the tank maximum capcity piece.=IF(OR(ABS(T14)>10%,S14>20000),"Y","N")Well I'd appreciate any help that anyone could offer. This is my first post on the forum so let me know if I need to add more details. Thanks.

 

[whosphil]

I kind of understand what youre asking, but it would be easier if their was a picture representation of the data. Correct me if I am wrong, you want 3 if statements?

[(IF tank 1 maximum capacity is less than 500,000 and the difference in inventory is greater than the absolute value of 10% or 20,000)

OR

(IF the tanks maximum capacity is between 500,000 and 1,000,000 and the difference in inventory is greater than the absoulte value of 20% or 100,000)

OR

(IF the maximum tank capacity is greater than 1,000,000 or the difference in inventory is greater than the absolute value of 30% or 300,000)]

Then "Y"

Else "N"

Is this what you mean?

 

[jenex11]

Hello whosphil What you described is exactly what I'm looking for.

 

[Snakehips]

jenex11,

Welcome to MrExcel.

You do not give any clue as to the cell that holds the tank capacity.

The below assumes that tank capacity is in cell R14 ??? Edit if incorrect.


=IF(OR(AND(R14>=10^6,OR(ABS(T14)>30%,S14>3*10^5)),AND(R14<10^6,OR(ABS(T14)>20%,S14>10^5)),AND(R14<5*10^5,OR(ABS(T14)>10%,S14>2*10^4))),"Y","N")

Hope that helps.

 

[jenex11]

Hello Snakehips

I think your formula is really great but I think there is a small issue.

In my spreadsheet in R14 it's 2,000,000 and T14 is 7.8% but S14 is -1,039,360 and the formula is returning a N when it should return a Y because it should be the absolute value of T14 and S14 and I think the formula is returning just the absolute value of T14.

I think that's my fault for not clarifying earlier. I appreciate your help.

 

[whosphil]

So what is the difference in inventory? abs(Tank - 500,000)? and 10% of tanks capacity?


[(IF tank 1 maximum capacity is less than 500,000 and the difference in inventory is greater than the absolute value of 10% or 20,000)

 

[Snakehips]

Hello Snakehips

I think your formula is really great but I think there is a small issue.

In my spreadsheet in R14 it's 2,000,000 and T14 is 7.8% but S14 is -1,039,360 and the formula is returning a N when it should return a Y because it should be the absolute value of T14 and S14 and I think the formula is returning just the absolute value of T14.

I think that's my fault for not clarifying earlier. I appreciate your help.

Then maybe....

=IF(OR(AND(R14>=10^6,OR(ABS(T14)>30%,ABS(S14)>3*10^5)),AND(R14<10^6,OR(ABS(T14)>20%,ABS(S14)>10^5)),AND(R14<5*10^5,OR(ABS(T14)>10%,ABS(S14)>2*10^4))),"Y","N")

 

[jenex11]

The formula works. Thank you very much for your help.