Home 
Search 
Today's Posts 
#1




Problem with adding "+1" to one cell while another cell decreased by "1"?
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 
#2




Problem with adding "+1" to one cell while another cell decreased by "1"?
Hi Michael
With your total in A1 =MAX(0,6A1)  Regards Roger Govier "Michael Slater" wrote in message ... 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 
#3




Problem with adding "+1" to one cell while another cell decreased
See if this works for you (assuming total is in cell A1):
=6A1 Or, to avoid negative numbers: =MAX(6A1,0) HTH, Elkar "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 
#4




Problem with adding "+1" to one cell while another cell decreased by "1"?
Guys,
That worked great. Thanks alot! Mike "Michael Slater" wrote in message ... 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 
#5




Problem with adding "+1" to one cell while another cell decreased by "1"?
=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 
#6




Problem with adding "+1" to one cell while another cell decreased by "1"?
Too simple and elegant for me<g
Gord On Fri, 27 Jul 2007 17:39:07 +0100, "Roger Govier" wrote: Hi Michael With your total in A1 =MAX(0,6A1) 
#7




Problem with adding "+1" to one cell while another cell decreased by "1"?
This may sound like a sophmoroic question, but I have couple of books on
Excel 2007. Microsoft's "Excel 2007, Inside Out", and John Walkenbach's "Excel 2007 Formulas". After reading and applying the "MAX" function that was posted here (Thank you again!), I started looking up this function in both of theses books (which I did reference, in vain, in the first place), and, maybe it's me (and I'm sure it is), but from the description in these books, I still have NO IDEA how this function applys in this instance. Can anyone recommend some reference source that spells these things out so that a newb like myself might understand it? Thanks Again, Mike "Michael Slater" wrote in message ... 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 
#8




Problem with adding "+1" to one cell while another cell decreased by "1"?
Gord,
Does that do the same thing as the other replies that were posted? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... =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 
#9




Problem with adding "+1" to one cell while another cell decreased by "1"?
The MAX function just returns the largest value from the list (in this
case of two numbers  either 0 or 6A1). Run through some possible values of A1  if A1 is 3 then 6A1 is also 3, and as this is larger than 0 this is what will be returned. If A1 is 7, then 6A1 is 1 which is less than 0, so 0 will be returned in this case. Does it make more sense now? Hope this helps. Pete On Jul 27, 8:20 pm, "Michael Slater" wrote: This may sound like a sophmoroic question, but I have couple of books on Excel 2007. Microsoft's "Excel 2007, Inside Out", and John Walkenbach's "Excel 2007 Formulas". After reading and applying the "MAX" function that was posted here (Thank you again!), I started looking up this function in both of theses books (which I did reference, in vain, in the first place), and, maybe it's me (and I'm sure it is), but from the description in these books, I still have NO IDEA how this function applys in this instance. Can anyone recommend some reference source that spells these things out so that a newb like myself might understand it? Thanks Again, Mike "Michael Slater" wrote in message ... 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 Hide quoted text   Show quoted text  
#10




Problem with adding "+1" to one cell while another cell decreased by "1"?
Pete,
Thank you. That does make sense now. I knew it was my inability to wrap my brain around it. It's just the way it's used in this formula, as opposed to how the MAX function is described in the books and help files in Excel that confused me. I suppose a book describing every possible use of functions, in layman's terms, would require a wheelbarrow to lug around. I really do prefer to find the answers to these problems myself, whenever possible. I guess there's no substitute for experience after all. Thanks again, Mike "Pete_UK" wrote in message oups.com... The MAX function just returns the largest value from the list (in this case of two numbers  either 0 or 6A1). Run through some possible values of A1  if A1 is 3 then 6A1 is also 3, and as this is larger than 0 this is what will be returned. If A1 is 7, then 6A1 is 1 which is less than 0, so 0 will be returned in this case. Does it make more sense now? Hope this helps. Pete On Jul 27, 8:20 pm, "Michael Slater" wrote: This may sound like a sophmoroic question, but I have couple of books on Excel 2007. Microsoft's "Excel 2007, Inside Out", and John Walkenbach's "Excel 2007 Formulas". After reading and applying the "MAX" function that was posted here (Thank you again!), I started looking up this function in both of theses books (which I did reference, in vain, in the first place), and, maybe it's me (and I'm sure it is), but from the description in these books, I still have NO IDEA how this function applys in this instance. Can anyone recommend some reference source that spells these things out so that a newb like myself might understand it? Thanks Again, Mike "Michael Slater" wrote in message ... 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 Hide quoted text   Show quoted text  
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
change "true" and "false" to "availble" and "out of stock"  Excel Worksheet Functions  
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!!  Excel Discussion (Misc queries)  
HELP on "left","right","find","len","substitute" functions  Excel Discussion (Misc queries)  
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next  New Users to Excel  
Complex if test program possible? If "value" "value", paste "value" in another cell?  Excel Discussion (Misc queries) 