Using if statements to look at previous items

Microsoft Excel 2003 (full product)
July 7, 2010 at 11:20:44
Specs: Windows XP
I have been working on this for 3+months and am now at my wits end on the last part of my spreadsheet. I am not sure how to paste a picture on here so I will do my best to lay it out.

     A            B          C          D        E        F          G         H  
3  BlisterPack  Standard  Standard  Standard  Standard  Standard  Standard  Standard
4  84           100        0          211      211       141         321       364

A3 threw H3

Under each of these colums the operator can select Standard, Lumberjack, or Blister pack

A4 threw H4

These colums is where the hourly goal needs to go. This is where I have problems. If during a shift if the operator starts on Standard product and then 5 or 6 hours into the shift they move into Blister pack I need to average out the goal and change future hours.

D2 holds the number of labor hours that went into the line.

This is what I have in the last cell for the 8th hour (note its a ton of IF statements)

=IF(I3="Standard",D2*Sheet1!B8,IF(I3="Lumberjack",D2*Sheet1!B9,IF(I3="Blister Pack",D2*Sheet1!B10,IF(H3="Standard",D2*Sheet1!B8,IF(H3="Lumberjack",D2*Sheet1!B9,IF(H3="Blister Pack",D2*Sheet1!B10,IF(G3="Standard",D2*Sheet1!B8,IF(G3="Lumberjack",D2*Sheet1!B9,))))))))+IF(G3="Blister Pack",D2*Sheet1!B10,IF(F3="Standard",D2*Sheet1!B8,IF(F3="Lumberjack",D2*Sheet1!B9,)))+IF(F3="Blister Pack",D2*Sheet1!B10,IF(E3="Standard",D2*Sheet1!B8,IF(E3="Lumberjack",D2*Sheet1!B9,)))+IF(E3="Blister Pack",D2*Sheet1!B10,IF(D3="Standard",D2*Sheet1!B8,IF(D3="Lumberjack",D2*Sheet1!B9,)))+IF(D3="Blister Pack",D2*Sheet1!B10,IF(C3="Standard",D2*Sheet1!B8,IF(C3="Lumberjack",D2*Sheet1!B9,)))+IF(C3="Blister Pack",D2*Sheet1!B10,IF(B3="Standard",D2*Sheet1!B8,IF(B3="Lumberjack",D2*Sheet1!B9,)))+IF(B3="Blister Pack",D2*Sheet1!B10,)

I have the rates set on Sheet 1 so it will be easier to change the rates when needed. I know what its doing wrong it is taking all the positives and adding them together. I don't know how to end it after it finds the first one as I am limited on the number of If statements it will let me use. If needed I can upload a picture of the spreadsheet this evening from home or the file itself anything to help get this up and running.

See More: Using if statements to look at previous items

July 7, 2010 at 11:41:26
Its not very clear whats going on since there are cells missing and references to 'Sheet1' in the formula. Maybe the image or file will clear things up...

Looks like you're adding 6 items. To debug your formula, I would put these in 6 cells and make sure they are each doing what they should be doing.

=IF(I3="Standard",D2*Sheet1!B8,IF(I3="Lumberjack",D2*Sheet1!B9,IF(I3="Blister Pack",D2*Sheet1!B10,IF(H3="Standard",D2*Sheet1!B8,IF(H3="Lumberjack",D2*Sheet1!B9,IF(H3="Blister Pack",D2*Sheet1!B10,IF(G3="Standard",D2*Sheet1!B8,IF(G3="Lumberjack",D2*Sheet1!B9,))))))))

=IF(G3="Blister Pack",D2*Sheet1!B10,IF(F3="Standard",D2*Sheet1!B8,IF(F3="Lumberjack",D2*Sheet1!B9,)))


Also, I see that you're just looking up the same three cells on Sheet1... why not use vlookup function instead of if function? For example...

D2*vlookup(I3,sheet1!$A8:B10,2,0) This would replace the whole first if statement A8 = "Standard" etc... so if I3 = "Standard" it would find it in A8 and would result in the value of B8.

Report •
Related Solutions

Ask Question