=IF(A95,"",IF(ISNA(LOOKUP(A9,{0,1,2,3,4,5},{0,5,4 ,3,2,1})),"",LOOKUP(A9,{0,1,2,3,4,5},{0,5,4,3,2,1} )))

Assuming A9 is Totals cell.


Gord Dibben MS Excel MVP


On Fri, 27 Jul 2007 12:10:34 -0400, "Michael Slater"
wrote:

I have a column that shows employees working on a given day. The bottom
cell of that column "total"s the number of employees working.

I would like to have the cell below the "total" cell, which would be
"overtime shifts available", increase by "1" for each decrease in the
"total" cell, above, when it goes below "6".

So, if the "Total" cell is "5", the "Overtime" cell is "1".
If the "Total" cell is "4", the "Overtime" cell is "2", etc...

Using an "IF" formula, I've gotten it to indicate "1" when the "Total" cell
is "5", but I'm lost after that.

Any help appreciated.

Mike