How do I make this formula-if cell empty +1 if cell has any value -1

On Jan 27, 2:50*pm, Rae wrote:
How do I make this formula-if cell empty +1 if cell has
any value -1

First, you need to decide if you mean "really empty" (no formula and
no value) or "appears empty" (no formula, or a formula that returns
"").

Use ISBLANK(A1) to test for "really empty". Use A1="" to test for
"appears empty". I use the latter because it is more general,
covering both cases.

=if(A1="", 1, -1)

If you want the "+" sign in the first case, you can use the custom
format "+0;-0" (without the quotes).

=IF(A1="","+1","-1")


Gord Dibben MS Excel MVP


On Tue, 27 Jan 2009 14:50:12 -0800, Rae
wrote:

How do I make this formula-if cell empty +1 if cell has any value -1

Is it possible to attach the document I am trying to work on? I know I am
probably not explaining very well. I have manually entered the information,
but know there is a way for excel to figure it out for me so I don't have to
do that.

"Gord Dibben" wrote:

=IF(A1="","+1","-1")


Gord Dibben MS Excel MVP


On Tue, 27 Jan 2009 14:50:12 -0800, Rae
wrote:

How do I make this formula-if cell empty +1 if cell has any value -1

what I am trying to do is calculate the number of people I have on duty.
This is on a schedule showing vacation, sick, holiday, ect. So before I make
my assignments, I want to know how many bodies I have available to assign to
a position. When the cell is empty, it should count as 1 body, if I have put
V, H, S or something in the cell, I want it to take away from the total
available to me.

"joeu2004" wrote:

On Jan 27, 2:50 pm, Rae wrote:
How do I make this formula-if cell empty +1 if cell has
any value -1

First, you need to decide if you mean "really empty" (no formula and
no value) or "appears empty" (no formula, or a formula that returns
"").

Use ISBLANK(A1) to test for "really empty". Use A1="" to test for
"appears empty". I use the latter because it is more general,
covering both cases.

=if(A1="", 1, -1)

If you want the "+" sign in the first case, you can use the custom
format "+0;-0" (without the quotes).

Some people upload their file to a file hosting website, then provide the
link in a message. Be aware that you are limiting your potential solution
providers because many people are concerned about viruses. If you go this
route, make sure you explain how providing the file helps get you a
solution -- ie, where you are having trouble, what you have tried, what you
want.

To maximize your chances of getting to the right solution quickly, your best
bet is to explain in English why the proposed solutions don't work, and what
solution you need instead.

For example, what was wrong with the formula Gord gave you?

Regards,
Fred.

"Rae" wrote in message
...
Is it possible to attach the document I am trying to work on? I know I am
probably not explaining very well. I have manually entered the
information,
but know there is a way for excel to figure it out for me so I don't have
to
do that.

"Gord Dibben" wrote:

=IF(A1="","+1","-1")


Gord Dibben MS Excel MVP


On Tue, 27 Jan 2009 14:50:12 -0800, Rae
wrote:

How do I make this formula-if cell empty +1 if cell has any value -1

So you don't want the actual text "+1" or "-1"

Where do you determine the total number that work for you?

Assuming you have a total number of people in a cell, say D1 = 10

In D2 enter =IF(A1="",$D$1,$D$1-1)

If A1 is empty then you will have 10 people available, if V,H or S then you
have 9 people available.

Copy down if necessary.

If this is not sufficient, upload it to http://www.savefile.com/ then post
the URL you will be given after you upload the file.


Gord


On Tue, 27 Jan 2009 15:50:00 -0800, Rae
wrote:

what I am trying to do is calculate the number of people I have on duty.
This is on a schedule showing vacation, sick, holiday, ect. So before I make
my assignments, I want to know how many bodies I have available to assign to
a position. When the cell is empty, it should count as 1 body, if I have put
V, H, S or something in the cell, I want it to take away from the total
available to me.

"joeu2004" wrote:

On Jan 27, 2:50 pm, Rae wrote:
How do I make this formula-if cell empty +1 if cell has
any value -1

First, you need to decide if you mean "really empty" (no formula and
no value) or "appears empty" (no formula, or a formula that returns
"").

Use ISBLANK(A1) to test for "really empty". Use A1="" to test for
"appears empty". I use the latter because it is more general,
covering both cases.

=if(A1="", 1, -1)

If you want the "+" sign in the first case, you can use the custom
format "+0;-0" (without the quotes).

When I put in the fromula from Gord, it came up with an error message. This
is what I explained to Joe. Hope it makes sense.

(What I am trying to do is calculate the number of people I have on duty.
This is on a schedule showing vacation, sick, holiday, ect. So before I make
my assignments, I want to know how many bodies I have available to assign to
a position. When the cell is empty, it should count as 1 body, if I have put
V, H, S or something in the cell, I want it to take away from the total
available to me.)

"Fred Smith" wrote:

Some people upload their file to a file hosting website, then provide the
link in a message. Be aware that you are limiting your potential solution
providers because many people are concerned about viruses. If you go this
route, make sure you explain how providing the file helps get you a
solution -- ie, where you are having trouble, what you have tried, what you
want.

To maximize your chances of getting to the right solution quickly, your best
bet is to explain in English why the proposed solutions don't work, and what
solution you need instead.

For example, what was wrong with the formula Gord gave you?

Regards,
Fred.

"Rae" wrote in message
...
Is it possible to attach the document I am trying to work on? I know I am
probably not explaining very well. I have manually entered the
information,
but know there is a way for excel to figure it out for me so I don't have
to
do that.

"Gord Dibben" wrote:

=IF(A1="","+1","-1")


Gord Dibben MS Excel MVP


On Tue, 27 Jan 2009 14:50:12 -0800, Rae
wrote:

How do I make this formula-if cell empty +1 if cell has any value -1

On Jan 27, 3:46*pm, Rae wrote:
Is it possible to attach the document I am trying to
work on? *I know I am probably not explaining very well.
*I have manually entered the information, but know there
is a way for excel to figure it out for me so I don't
have to do that.

Be extremely wary of suggestions to upload your workbook to a website.

From your description in other parts of this thread, it sounds like
the workbook contains personnel data -- perhaps names, if not other
personal information.

You have an obligation -- a moral one, if not a legal one -- to keep
that information private.

http://www.savefile.com/files/1984272
If you look at X43 it shows a total for 3 other cells. What I want to do is
have it calculate the total automatically using cells X8 to X18, X21 to X30
and X33 to X42. Does this help? I REALLY APPRECIATE YOUR
HELP!!!!!!!!!!!!!!!!!

"Gord Dibben" wrote:

So you don't want the actual text "+1" or "-1"

Where do you determine the total number that work for you?

Assuming you have a total number of people in a cell, say D1 = 10

In D2 enter =IF(A1="",$D$1,$D$1-1)

If A1 is empty then you will have 10 people available, if V,H or S then you
have 9 people available.

Copy down if necessary.

If this is not sufficient, upload it to http://www.savefile.com/ then post
the URL you will be given after you upload the file.


Gord


On Tue, 27 Jan 2009 15:50:00 -0800, Rae
wrote:

what I am trying to do is calculate the number of people I have on duty.
This is on a schedule showing vacation, sick, holiday, ect. So before I make
my assignments, I want to know how many bodies I have available to assign to
a position. When the cell is empty, it should count as 1 body, if I have put
V, H, S or something in the cell, I want it to take away from the total
available to me.

"joeu2004" wrote:

On Jan 27, 2:50 pm, Rae wrote:
How do I make this formula-if cell empty +1 if cell has
any value -1

First, you need to decide if you mean "really empty" (no formula and
no value) or "appears empty" (no formula, or a formula that returns
"").

Use ISBLANK(A1) to test for "really empty". Use A1="" to test for
"appears empty". I use the latter because it is more general,
covering both cases.

=if(A1="", 1, -1)

If you want the "+" sign in the first case, you can use the custom
format "+0;-0" (without the quotes).

The document is classified as public by GRAMA so it should not be a problem
posting it.

"joeu2004" wrote:

On Jan 27, 3:46 pm, Rae wrote:
Is it possible to attach the document I am trying to
work on? I know I am probably not explaining very well.
I have manually entered the information, but know there
is a way for excel to figure it out for me so I don't
have to do that.

Be extremely wary of suggestions to upload your workbook to a website.

From your description in other parts of this thread, it sounds like
the workbook contains personnel data -- perhaps names, if not other
personal information.

You have an obligation -- a moral one, if not a legal one -- to keep
that information private.

On Jan 27, 3:50*pm, Rae wrote:
what I am trying to do is calculate the number of people
I have on duty. *This is on a schedule showing vacation,
sick, holiday, ect. *So before I make my assignments, I
want to know how many bodies I have available to assign
to a position. *When the cell is empty, it should count
as 1 body, if I have put V, H, S or something in the cell,
I want it to take away from the total available to me.

I wonder if you would be happier with a different approach altogether.

Let's go back to your first sentence: you want to calculate the
number of people you can have on duty. That is calculated by an
"empty" cell in a column (or row) of data entries.

Suppose the "V, H, S or something" is in the cells A2:A100 for each of
99 people. Then the following would count the number of empty cells
(and cells that contain ""):

=countblank(A2:A100)

PS: I want to reiterate that I think it is prudent to count cells
that contain "", not just "empty" cells (cells with no formula and no
value). if it is very common to have logic like: =if(condition, "",
value).

You have not given permission to modify the file so can open in read-only.

I definitely don't understand the method used to count the number of people
available.

I can keep looking at it but not sure what I can do.


Gord

On Tue, 27 Jan 2009 16:58:07 -0800, Rae
wrote:

http://www.savefile.com/files/1984272
If you look at X43 it shows a total for 3 other cells. What I want to do is
have it calculate the total automatically using cells X8 to X18, X21 to X30
and X33 to X42. Does this help? I REALLY APPRECIATE YOUR
HELP!!!!!!!!!!!!!!!!!

"Gord Dibben" wrote:

So you don't want the actual text "+1" or "-1"

Where do you determine the total number that work for you?

Assuming you have a total number of people in a cell, say D1 = 10

In D2 enter =IF(A1="",$D$1,$D$1-1)

If A1 is empty then you will have 10 people available, if V,H or S then you
have 9 people available.

Copy down if necessary.

If this is not sufficient, upload it to http://www.savefile.com/ then post
the URL you will be given after you upload the file.


Gord


On Tue, 27 Jan 2009 15:50:00 -0800, Rae
wrote:

what I am trying to do is calculate the number of people I have on duty.
This is on a schedule showing vacation, sick, holiday, ect. So before I make
my assignments, I want to know how many bodies I have available to assign to
a position. When the cell is empty, it should count as 1 body, if I have put
V, H, S or something in the cell, I want it to take away from the total
available to me.

"joeu2004" wrote:

On Jan 27, 2:50 pm, Rae wrote:
How do I make this formula-if cell empty +1 if cell has
any value -1

First, you need to decide if you mean "really empty" (no formula and
no value) or "appears empty" (no formula, or a formula that returns
"").

Use ISBLANK(A1) to test for "really empty". Use A1="" to test for
"appears empty". I use the latter because it is more general,
covering both cases.

=if(A1="", 1, -1)

If you want the "+" sign in the first case, you can use the custom
format "+0;-0" (without the quotes).

On Jan 27, 4:58*pm, Rae wrote:
http://www.savefile.com/files/1984272
If you look at X43 it shows a total for 3 other cells.
What I want to do is have it calculate the total
automatically using cells X8 to X18, X21 to X30
and X33 to X42. Does this help?

So why not:

=countblank(X8:X18)+countblank(X21:X30)+countblank (X33:X42)

On Jan 27, 5:33*pm, Gord Dibben <gorddibbATshawDOTca wrote:
I definitely don't understand the method used to count
the number of people available.

Presumably, X43 is something like

=X6+X19+X31

On Jan 27, 7:24*pm, I wrote:
On Jan 27, 5:33*pm, Gord Dibben <gorddibbATshawDOTca wrote:

I definitely don't understand the method used to count
the number of people available.

Presumably, X43 is something like

=X6+X19+X31

And X6, X19, X31 (etc) seem to be the sum of cells in that group below
(excl the sgt) that do not have certain flags like X, O ("oh"), M and
BG. But cells that have a number, a number and T, or a number and R
are counted as on-duty. Those list are examples; they may not be
exhaustive. I am not going to study the entire worksheet.

In any case, I don't know if that's the programming that Rae wants to
change, or Rae is simply struggling to understand the logic.
Presumably Rae can read the formulas.