Forum Discussion
Multiple IF statements, I think
MattP817 I think i can do 1 better. If you unmerge the cells (at least in column G) then this formula can be pasted on the entire column (paste formula only to maintain other formatting):
=IFS(A6="Time","Units/Hr.",B6="PO#","Unit Rate",A6="","",NOT((A6<=2)*(A6>=0)),"",(E5>" ")*(E6=""),LET(a,XLOOKUP(C6,$C$1:$C5,$F$1:F5,0,0,-1),IF(F6<a,F6,F6-a)),F6<F5,F6,TRUE,(F6-F5))In the attached I unmerged cells in rows 31-34 and tested it there and seems to work well. Basically instead of copy and paste each section you will be able to copy and paste on the entire column (paste formula only).
mtarler So I got the formula plugged in for the 6:00 am hour to the following 5:00 am hour. When I move to the next day, it is not jumping to the line that is prior to the "WTN".
I need to subtract the light blue cell (F1184) from the red cell (F1175).
Is there a way to have the formula skip anything >"" in the E column when production resumes, but still calculate the formula in during the changeover event?
b) can I assume columns C and D will be #s for non-test time?
c) looks like you need to have special condition for 5am in general.
d) I'm not sure why/how the formula is failing in the above case. From what I see I would expect it to fail because of the merged cell/line just above it but that isn't what appears to be happening
e) i assume you mean subtract red from blue (but in this case it is 0 either way). That said, if it will always be 0 (i.e. it starts up where it left off after testing) then that could make this all much easier.
- It won't always be 0 after a changeover. They might finish the changeover early in the hour and still produce units before the next hour.
MattP817 don't know if you figured something out but here is an option:
=IFS((E1173="WTN")*(E1174=""),INDEX(F:F,MAX(ROW(F:F)*(ROW(F:F)<ROW())*(E:E=""))),F1174<F1173,F1174,TRUE,(F1174-F1173))if you have excel 365 you might try using this as it might be a bit more efficient:
=IFS((E1183>" ")*(E1184=""),F1184-XLOOKUP(C1184,$C$1:$C1183,$F1:$F1183,0,0,-1),F1184<F1183,F1184,TRUE,(F1184-F1183))both examples attached.
My concern is that it does NOT take into account the case (if possible) where the value of the 1st row of the exception/testing is still based on the material results. I didn't see any examples that would matter because most cases the value goes back to 0 after a changeover event. For example on line 290 the value goes up to 100 and then the subsequent lines start with 1, 2, etc... But that is a w.t.n. even so you may need to tweak this for which events it should consider also. i hope this at least gives you ideas on how you find that previous line and apply the rules you need accordingly.
mtarler Thanks for the update! I have a couple more issues. I have the formula loaded (XLOOKUP). I have a few errors I am trying to work out. I owe you big time for all of your help!
- On G1072 it is looking for the previous time we ran that material and it is pulling the data from cell F270 giving me a negative.
- Same thing on G1036, pulling from F891.
- On cell G714, we switched POs, but ran the same material. How would I be able to fix an error like this
a) can you share the sheet? Attached
b) can I assume columns C and D will be #s for non-test time? Column C is a material number that they are producing. Column D is just an informational column for the production line. E is a changeover event. F is the last unit # that was produced during that hour. G is a run rate based off the difference between the previous hour to current hour. H is a target based on the material number (VLOOKUP). I is the difference on units ran vs target (G-H). J is based off the units ran and the weight of the units they are producing.
c) looks like you need to have special condition for 5am in general.
d) I'm not sure why/how the formula is failing in the above case. From what I see I would expect it to fail because of the merged cell/line just above it but that isn't what appears to be happening
e) i assume you mean subtract red from blue (but in this case it is 0 either way). That said, if it will always be 0 (i.e. it starts up where it left off after testing) then that could make this all much easier. Yes. My apologies.