Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 27th 07, 05:10 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 17
Default 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   Report Post  
Old July 27th 07, 05:39 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 57
Default Problem with adding "+1" to one cell while another cell decreased by "1"?

Hi Michael

With your total in A1
=MAX(0,6-A1)

--
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   Report Post  
Old July 27th 07, 05:40 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 964
Default Problem with adding "+1" to one cell while another cell decreased

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


"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   Report Post  
Old July 27th 07, 06:55 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 17
Default 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   Report Post  
Old July 27th 07, 06:57 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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   Report Post  
Old July 27th 07, 07:32 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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,6-A1)


  #7   Report Post  
Old July 27th 07, 08:20 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 17
Default 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   Report Post  
Old July 27th 07, 08:21 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 17
Default 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   Report Post  
Old July 27th 07, 09:01 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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 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

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   Report Post  
Old July 27th 07, 09:39 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 17
Default 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 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


"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 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

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change "true" and "false" to "availble" and "out of stock" inthestands Excel Worksheet Functions 2 July 19th 07 07:05 PM
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! [email protected] Excel Discussion (Misc queries) 3 January 5th 07 02:18 PM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 02:23 AM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM
Complex if test program possible? If "value" "value", paste "value" in another cell? jseabold Excel Discussion (Misc queries) 1 January 30th 06 10:01 PM


All times are GMT +1. The time now is 06:27 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017