OK, Thanks again Sandy.


"Sandy Mann" wrote in message
...
Hi Martin,

Bernie's formula will take care of double, (or more), digit numbers after
the text ot letter and a space, your will only capture the final digit
even if there are more.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"MartinW" wrote in message
...
Thanks Sandy,

That's cleared up the blanks issue very well.

I am intrigued as to why you prefer the VALUE(MID approach
to the RIGHT approach.

VALUE(MID requires 3 characters in the input cells
i.e Tspace4

RIGHT will handle Tspace 4, T4 or 4 or even a typo like Tspacespace4

Or am I missing something else again?

Regards
Martin

"Sandy Mann" wrote in message
...
"MartinW" wrote in message
...
Can somebody else help out here?


As an academic exercise - (I think that Bernie's formulas is the way to
go) - this should handle blank cells or cells returning an empty string:

=(C9-(SUM(IF(F9:AD9<"",RIGHT(F9:AD9)*1))/2))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"MartinW" wrote in message
...
Sorry that doesn't handle blank cells, although it will work if
you put a zero in the blank cells.

Can somebody else help out here?
I think an ISTEXT or maybe an IF(ISTEXT
can be worked into this array formula to handle the blanks
but I can't seem to get the syntax right.

The array formula I started with is
=(C9-(SUM(RIGHT(F9:AD9)*1)/2))

Regards
Martin


"MartinW" wrote in message
...
Hi Aundria,

Try this formula in AE9
=(C9-(SUM(RIGHT(F9:AD9)*1)/2))
This is an array formula and must be committed
with Ctrl+Shift+Enter and not just enter

HTH
Martin


"Aundria123" wrote in message
...
Hello,

I'm a new user to excel and forumlas. I have a column that has a
total
number of units in C9. In column C8 are names of people. I have to
either
put next to the names T 2, T 4, E 2 or E4 (F9 to AD9). I am using
the
following formula to subtract from the total number of units for each
day in
the month.

Formula in Cell AE9: =(C9-(SUM(F9:AD9))/2)

This works perfectly for me when I only insert number 2 or 4. In
column
AE,it gives me the number of UNUSED UNITs.

Well now my boss wants me to enter the letter T or E with the
NUMBERS, and I
don't know how to adjust this forumla to IGNORE the letter so that
the
formula continues to work. Right now when I add a letter to the
cell, the
formula ignores the number.

I hope this makes sense as I'm desperate for a solution. I have
digged
through a bunch of help sites, but I'm not technical enough to
understand.

Thank you for your help!