help with formula

J

joeu2004

How do I make this formula-if cell empty +1 if cell has
any value -1
Click to expand...

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).
 
R

Rae

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.
 
R

Rae

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.
 
F

Fred Smith

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.
 
G

Gord Dibben

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
 
R

Rae

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.)
 
J

joeu2004

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.
Click to expand...

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.
 
J

joeu2004

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.
Click to expand...

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).
 
G

Gord Dibben

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
 
J

joeu2004

Presumably, X43 is something like

=X6+X19+X31
Click to expand...

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.
 
Top