Adding and subtracting text

[klancey]

Heres my problem. If a cell has text in it I need my totals colum to add 1 to the total, if a cell is empty (no text) I need it to subtract 1 from my total. I'm going crazy!
If I could get totals to subtract or add (depending on what column the entry is in ) 2 or 3 if the names are seperated by a / or something that would be better. If column A has a text in it, column C (the total column) would be raised by 1. If column B had text in it, column C would be lowered by 1. If Column A had 2 names in it seperated by some character, column C would go up 2, 3 names 3, etc. Same for column B but it would be lowered by 1,2, 3 etc depending on the number of names.

[ This Message was edited by: klancey on 2002-05-05 16:15 ]

[ This Message was edited by: klancey on 2002-05-05 16:41 ]

[Nimrod]

I find your posting hard to understand but i'm thinking a Countblank or counta formula should work.
Does this help ? If not please clarify what your looking for.

[Aladin Akyurek]

On 2002-05-05 16:15, klancey wrote:
Heres my problem. If a cell has text in it I need my totals colum to add 1 to the total, if a cell is empty (no text) I need it to subtract 1 from my total. I'm going crazy!
a b total
88

smith 89
jones/wilson 87

Like that example. If I could get totals to subtract or add (depending on what column the entry is in ) 2 or 3 if the names are seperated by a / or something that would be better. The formatting is lost on my example when submitted so add some spaces.

[ This Message was edited by: klancey on 2002-05-05 16:15 ]

What I see is this:

{"","",88;
smith,"",89;
"","jones/wilson",87}

where "" stands for a blank cell.

smith makes 88 become 89.
How come the third total is 87, while we have this jones/wilson in that row?

Care to specify the rules for adding and subtracting?

[klancey]

On 2002-05-05 16:23, Nimrod wrote:
I find your posting hard to understand but i'm thinking a Countblank or counta formula should work.
Does this help ? If not please clarify what your looking for.

I tried to clatrify some. I hope it helps.

[klancey]

Aladin-the number moves according to how many names are in the cell, and wether the names are in a add or subtract column. I don't know if its possible to do that, it would be an added bonus if so.

[Nimrod]

If you don't mind me asking could you tell me what the purpose of this spreadsheet is.

[ This Message was edited by: Nimrod on 2002-05-05 17:22 ]

[Aladin Akyurek]

On 2002-05-05 16:49, klancey wrote:
Aladin-the number moves according to how many names are in the cell, and wether the names are in a add or subtract column. I don't know if its possible to do that, it would be an added bonus if so.

Lets say that A1: houses the following sample:

{"Names1","Names2";
"wr","";
"cxs","";
"deq/dem","tu";
"","tre";
"","tre/lkj";
"weq","";
"sda","";
"","";
"ara","fsg"}

In C1 enter: Total
In C2 enter:

=(LEN(A2)>0)+(LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))-(LEN(B2)>0)+(LEN(B2)-LEN(SUBSTITUTE(B2,"/","")))

In C3 enter and copy down:

=C2+((LEN(A3)>0)+(LEN(A3)-LEN(SUBSTITUTE(A3,"/",""))))-((LEN(B3)>0)+(LEN(B3)-LEN(SUBSTITUTE(B3,"/",""))))

This is what we get in column C:

{"Total";1;2;3;2;0;1;2;2;2}

Postscript. Empty cells do not affect the running total.

[ This Message was edited by: aladin akyurek on 2002-05-05 17:40 ]

[klancey]

That is a good formula but I am new to excel and dont understand what it means. Can you break it down for me without carpal tunnel syndrome setting in?

[ This Message was edited by: klancey on 2002-05-05 17:53 ]

[klancey]

Nimrod- its a census form for disabled kids who sometimes go out to the hospital. It keeps track of who is in the hospital, discharged, admitted, etc. Sometimes they go out for 1 day or 2 or the like, and we have to keep track.We also discharge and admit kids too. See http://www.saintmaryshome.org

[Aladin Akyurek]

On 2002-05-05 17:48, klancey wrote:
That is a good formula but I am new to excel and dont understand what it means. Can you break it down for me without carpal tunnel syndrome setting in?

[ This Message was edited by: klancey on 2002-05-05 17:53 ]

LEN(a-cell)>0 checks the length of the value in a-cell. If length 0 then 0, otherwise 1.

The SUBSTITUTE bit substitutes "" for each /, so determining how many names are in a cell.

This is a bit terse, but it would help.

Aladin