Multiple IF statements, I think

%3CLINGO-SUB%20id%3D%22lingo-sub-2985605%22%20slang%3D%22en-US%22%3EMultiple%20IF%20statements%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2985605%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20production%20run%20sheet%20with%20a%20column%20that%20indicates%20a%20clean.%20After%20the%20clean%2C%20when%20our%20operators%20enter%20the%20production%20unit%20%23%20they%20are%20on%2C%20I%20need%20it%20to%20reference%20the%20production%20unit%20%23%20they%20were%20on%20prior%20to%20the%20clean.%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ERight%20now%2C%20I%20have%20a%20Vlookup%20referencing%20the%20run%20rate%20based%20on%20the%20item%20%23%20(Column%20H).%3C%2FLI%3E%3CLI%3EThe%20operators%20manually%20enter%20the%20unit%20%23%20they%20are%20on%20(Column%20F).%3C%2FLI%3E%3CLI%3EI%20have%20an%20IF%20statement%20%3DIF(F1174%3CF1173%3E%3C%2FF1173%3E%3C%2FLI%3E%3CLI%3EIf%20there%20is%20a%20clean%20in%20column%20E%2C%20I%20would%20like%20to%20find%20a%20way%20to%20reference%20the%20previous%20unit%20%23%20in%20column%20F%20that%20they%20were%20on%20before%20the%20clean.%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3EPLEASE%20HELP%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MattP817_1-1637334944560.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328213i90AEB63DC9310BDA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MattP817_1-1637334944560.png%22%20alt%3D%22MattP817_1-1637334944560.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2985605%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2986316%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20statements%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2986316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1224084%22%20target%3D%22_blank%22%3E%40MattP817%3C%2FA%3E%26nbsp%3B%20I%20think%20this%20should%20work%20(untested).%26nbsp%3B%20Basically%20I%20check%20if%20the%20prior%20had%20the%20cleaning%20indication%20and%20this%20line%20doesn't%20and%20then%20find%20the%20max%20row%20that%20is%20less%20than%20this%20row%20and%20is%20blank%20in%20the%20cleaning%20indication.%26nbsp%3B%20I%20also%20switch%20to%20an%20IFS()%20statement%20but%20could%20be%20done%20as%20nested%20IF()%20statements.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFS((E1173%3D%22VTN%22)*(E1174%3D%22%22)%2CINDEX(F%3AF%2CMAX(ROW(F%3AF)*(ROW(F%3AF)%3CROW%3E%3C%2FROW%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2994605%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20statements%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2994605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BSo%20I%20got%20the%20formula%20plugged%20in%20for%20the%206%3A00%20am%20hour%20to%20the%20following%205%3A00%20am%20hour.%20When%20I%20move%20to%20the%20next%20day%2C%20it%20is%20not%20jumping%20to%20the%20line%20that%20is%20prior%20to%20the%20%22WTN%22.%3C%2FP%3E%3CP%3EI%20need%20to%20subtract%20the%20light%20blue%20cell%20(F1184)%20from%20the%20red%20cell%20(F1175).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20have%20the%20formula%20skip%20anything%20%26gt%3B%22%22%20in%20the%20E%20column%20when%20production%20resumes%2C%20but%20still%20calculate%20the%20formula%20in%20during%20the%20changeover%20event%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MattP817_0-1637609922314.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328709i5B5D6E05AC6DA5B4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MattP817_0-1637609922314.png%22%20alt%3D%22MattP817_0-1637609922314.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2995074%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20statements%2C%20I%20think%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2995074%22%20slang%3D%22en-US%22%3Ea)%20can%20you%20share%20the%20sheet%3F%3CBR%20%2F%3Eb)%20can%20I%20assume%20columns%20C%20and%20D%20will%20be%20%23s%20for%20non-test%20time%3F%3CBR%20%2F%3Ec)%20looks%20like%20you%20need%20to%20have%20special%20condition%20for%205am%20in%20general.%3CBR%20%2F%3Ed)%20I'm%20not%20sure%20why%2Fhow%20the%20formula%20is%20failing%20in%20the%20above%20case.%20From%20what%20I%20see%20I%20would%20expect%20it%20to%20fail%20because%20of%20the%20merged%20cell%2Fline%20just%20above%20it%20but%20that%20isn't%20what%20appears%20to%20be%20happening%3CBR%20%2F%3Ee)%20i%20assume%20you%20mean%20subtract%20red%20from%20blue%20(but%20in%20this%20case%20it%20is%200%20either%20way).%20That%20said%2C%20if%20it%20will%20always%20be%200%20(i.e.%20it%20starts%20up%20where%20it%20left%20off%20after%20testing)%20then%20that%20could%20make%20this%20all%20much%20easier.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a production run sheet with a column that indicates a clean. After the clean, when our operators enter the production unit # they are on, I need it to reference the production unit # they were on prior to the clean. 

  • Right now, I have a Vlookup referencing the run rate based on the item # (Column H).
  • The operators manually enter the unit # they are on (Column F).
  • I have an IF statement =IF(F1174<F1173,F1174,(F1174-F1173)) to calculate the hourly run rate. If they start a new product, it will take the new unit #.
  • If there is a clean in column E, I would like to find a way to reference the previous unit # in column F that they were on before the clean. 

PLEASE HELP  

 

MattP817_1-1637334944560.png

 

 

 

 

5 Replies

@MattP817  I think this should work (untested).  Basically I check if the prior had the cleaning indication and this line doesn't and then find the max row that is less than this row and is blank in the cleaning indication.  I also switch to an IFS() statement but could be done as nested IF() statements.

 

 

=IFS((E1173="WTN")*(E1174=""),INDEX(F:F,MAX(ROW(F:F)*(ROW(F:F)<ROW())*(E:E=""))),F1174<F1173,F1174,TRUE,(F1174-F1173))

 

 

 

@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?

 

MattP817_0-1637609922314.png

 

a) can you share the sheet?
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.

@mtarler 

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. 

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.