 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2007Problem 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 Hi Michael With your total in A1 =MAX(0,6-A1)  SignatureRegards Roger Govier >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. [quoted text clipped - 12 lines] > > Mike Too simple and elegant for me<g> Gord >Hi Michael > >With your total in A1 >=MAX(0,6-A1) But, if only I could always think in this way<bg> SignatureRegards Roger Govier > Too simple and elegant for me<g> > [quoted text clipped - 8 lines] >>With your total in A1 >>=MAX(0,6-A1) See if this works for you (assuming total is in cell A1): =6-A1 Or, to avoid negative numbers: =MAX(6-A1,0) HTH, Elkar > 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. [quoted text clipped - 12 lines] > > Mike Guys, That worked great. Thanks alot! Mike >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. [quoted text clipped - 12 lines] > > Mike =IF(A9>5,"",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 >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. [quoted text clipped - 12 lines] > >Mike Gord, Does that do the same thing as the other replies that were posted? > =IF(A9>5,"",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}))) > [quoted text clipped - 23 lines] >> >>Mike Returns a blank cell if "total" value is greater than 5 or less than 0 Returns a zero if no value in "total" Otherwise.......same returns as others. Gord >Gord, > [quoted text clipped - 27 lines] >>> >>>Mike 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 >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. [quoted text clipped - 12 lines] > > Mike The MAX function just returns the largest value from the list (in this case of two numbers - either 0 or 6-A1). Run through some possible values of A1 - if A1 is 3 then 6-A1 is also 3, and as this is larger than 0 this is what will be returned. If A1 is 7, then 6-A1 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 > 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 [quoted text clipped - 31 lines] > > - Show quoted text - 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 wheel-barrow 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 > The MAX function just returns the largest value from the list (in this > case of two numbers - either 0 or 6-A1). Run through some possible [quoted text clipped - 44 lines] >> >> - Show quoted text - The way it is being used here, though, with only two parameters, is equivalent to a conditional IF statement - it could be replaced with: =IF(6-A1<0,0,6-A1) Hope this helps further. Pete > Pete, > [quoted text clipped - 67 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.