Multiple condition formula

[Tybudd]

Hey Guys I need Help with Conditional Calculations on my spreadsheet, I think the problem is that the column where the formula needs to be, is also being checked by conditions according to the format. I'd like to explain the goal, show you what I attempted to do, see if you can explain to me why it didn't work, and suggest a correct formula for the goal, been at this for hours....

I have a production Spreadsheet.

• R=Production Order
• Q=Order Sequence
• S=Total Cases Produced in the Production order, can be placed on any row within that particular sequence of a Production Order
• W=Finished Cases

W=Finished Cases is the end Goal Problem Area, if I have a sequence of 4 items for a particular Production Order, the first 3 will be calculated by a formula to yield 95% of the Production Weight, that part is fine, I got that down, but the issue is what is needed with the last Finished Case Cell in the sequence, this cell needs to find the total (S) in relation to the row it is aligned on, then subtract it from the sum of the previous 3 in the sequence to note the remaining difference. Why? If the sheet was stagnant, I could just do a simple =total-sum(w8:w10) and get what I need, but I have to change info a lot, and order of display depending on the cells, so this is what I tried to do for an example to get a better understanding:

=SUMIF(R:R,[@[Production Run]],S:S)-sumifs(W:W,R:R,[@[Production Run]],Q:Q,<[@[Order of run]])

Destination of that formula would be at W10

Everything before the minus is good, I'm not sure if a formula of If(and then sum can work, but hopefully you can see the base logic of the goal.

thanks guys!!

PS tried to upload the file, it only allowed photo

 

[sandy666]

try to post link to the shared Excel file using OneDrive, GoogleDrive or any similar

 

[RoryA]

The last part of your SUMIFS wouldn't work. It would need to be:

=SUMIF(R:R,[@[Production Run]],S:S)-sumifs(W:W,R:R,[@[Production Run]],Q:Q,"<"&[@[Order of run]])

 

[Tybudd]

RoryA is the MVP for a reason!!!

"<"&

that part confused me, I always thought when you put "" around text, the formula only reads it as text or absolute values, what makes this more valid than just <[@[Order of run]

 

[Tybudd]

OK so continuing this scenario attempting to expand this formula, my 2nd IF function is not returning the value as True, when it should be:

=IF([@[Produced Weight]]="","",IF(AND(R:R=[@[Production Run]],Q:Q<[@[Order of run]]),SUMIF(R:R,[@[Production Run]],S:S)-SUMIFS(W:W,R:R,[@[Production Run]],Q:Q,"<"&[@[Order of run]]),ROUNDUP([@[Produced Weight]]*0.95/(+VLOOKUP([@[Product Made]],Table2[#Data],6,FALSE)),0.1)))

its the same scenario as the above spreadsheet photo, from the "here" cell.

Also here is the link Tracking Doc

 

[Tybudd]

I think the problem is my 2nd argument is incorrect, instead of just Column < Cell in that column, I need the argument to be something like not the highest value, checking to see if there is another value higher.

 

[Tybudd]

The last part of your SUMIFS wouldn't work. It would need to be:

=SUMIF(R:R,[@[Production Run]],S:S)-sumifs(W:W,R:R,[@[Production Run]],Q:Q,"<"&[@[Order of run]])

Is it possible to use that argument?

 

[RoryA]

Can you explain in plain English what the second IF function is supposed to achieve. Your current formula doesn't make any sense to me.

 

[Tybudd]

OK, that's what I figured, let me try....

With formula in W6, check to see what cells in Column R match R6, from the rows that match, check Column Q to see that it is not the highest value of the Rows that match.

Further breakdown
In this example W4, W5, both match W6, so within those 3 rows we are checking the number value in Column Q which is Q3=1 , Q4=2 , Q=3. of the 3 if the value in Q isn't the highest then return false.
So the Formula in W3 and W4 would return false, but W6 returns true.


Make sense?

 

[Tybudd]

IF(AND(R:R=[@[Production Run]],Q:Q<MAX([@[Order of run]])),"true","false")

so to eliminate some complexity, this is the only part of the formula that is the problem, so where I have the MAX still gives me an issue, because I want to check the argument in Q only in the range of Q, that matches the R argument. But in my formula it is will check the full Q column, how can I fix this to only look for this situation?