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

jenex11

New Member
Joined
May 21, 2014
Messages
4
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)


 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top