IF with nested AND and OR statements

[tycasey17]

[TABLE="class: grid, width: 750, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Rank[/TD]
[TD]ID#[/TD]
[TD]Code[/TD]
[TD]Status[/TD]
[TD]Gender[/TD]
[TD]Issue[/TD]
[TD]ORD DT[/TD]
[TD]Coat[/TD]
[TD]Pants[/TD]
[TD]Skirt[/TD]
[TD]SS Shirt[/TD]
[TD]LS Shirt[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]SGT[/TD]
[TD]12345[/TD]
[TD]TT[/TD]
[TD]TPU[/TD]
[TD]Male[/TD]
[TD][/TD]
[TD][/TD]
[TD]30xs[/TD]
[TD]26s[/TD]
[TD][/TD]
[TD]14.5[/TD]
[TD]13.5 x 32/33[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]PV2[/TD]
[TD]67890[/TD]
[TD]AA[/TD]
[TD]TPU[/TD]
[TD]Female[/TD]
[TD][/TD]
[TD]2015-1-1[/TD]
[TD]8WR[/TD]
[TD]8WR[/TD]
[TD]8WR[/TD]
[TD]8R[/TD]
[TD]8R[/TD]
[/TR]
</tbody>[/TABLE]






ISSUE Column needs to have one of the six possible entries:

NI = No Issue, is their rank is an officer then they won't get any issue or if their status is anything but TPU then they won't get anything;

NS = Need Sizes, if they meet the first requirement then of being enlisted and/or a TPU then if the Coat through LS Shirt cell are empty then I want the return to be NS

NTO = Need To Order, if the sizes are all in the cells and there is nothing in the ORD DT cell then I want the return to be NTO;

OO = On Order, if the sizes are all in the cells and a date is entered in the ORD DT then I want the return to be OO

PI = Partial Issue, this is for some stuff that has been given to the individual but not all of it. This is where my hang up is really coming into play. Because the for the males SS Shirt it is just a number and when I input if something has come in a I put he quantity, since the quantity will never be higher than 2 I tried doing a statement to only do equal to or greater than three but it didn't work. I tried using a numeric system but I am trying not to have my formulas be linked to an outside cells besides the ones providing direct data.

FQI = Full Quantity Issued, this is when all items that were ordered have come in and been received by the individual. The sum of this cell will always be 5. based on the cells for the above the numbers will correlate, for males it will be 1+2+0+1+1 (0 since they won't get a skirt) and females will be, 1+1+1+1+1.


I am wanting to have this determined by Male and Female and I have a sample of the formula I used on another section but the clothing is unisex and the sizes where all letter based and not alpha numeric like this is.

SAMPLE FORMULA FROM THE OTHER PAGE. It works just like I needed it to.
=IF($A3="","",
IF(OR(ISNUMBER(MATCH($B3,_OFF,0)),ISNUMBER(MATCH($E3,_NI,0))),"NI",
IF(AND(G3=0,ISBLANK(H3),ISBLANK(I3),ISBLANK(J3),ISBLANK(K3),ISBLANK(L3)),"NS",
IF(AND(G3=0,COUNTIF(H3:L3,"?*")=5),"NTO",
IF(AND(G3<>0,COUNTIF(H3:L3,"?*")=5),"OO",
IF(AND(G3<>0,COUNTIF(H3:L3,"?*")<=4,SUM(H3:L3)<=6),"PI",
IF(AND(G3<>0,SUM(H3:L3)=7),"FQI",
)))))))


If anyone would be able to assist I would appreciate the help. I would give you a sample of what I have done already except I am trying mostly to work on the PI argument to get that one figured out since that is the biggest hang up for me.

 

[tycasey17]

I got it figured out. If anyone wants to still post a formula feel free.

 

[Mackers]

Hi tycasey

Glad your issue has been solved.

Just as a tip, if you are checking ISBLANK(H3),ISBLANK(I3),ISBLANK(J3),ISBLANK(K3),ISBLANK(L3) you can instead check IF(LEN(H3&I3&J3&K3&L3)=0) - this might save you some time at some point.

Also, ISBLANK() will return FALSE for a cell that contains a formula, even if the formula returns an empty string, "". If you want the check to instead check if the cell looks blank, LEN()=0 of this will return TRUE.

Mackers