Bernie,

I found out that the "array enter a forumla" just puts the squirly brackets
around the formula. So when I do this, I still get the #VALUE! error. Any
ideas why?

I've looked at the Excel HELP and I've tried to rectify this error. If you
don't know why i'm getting the error, please just let me know so I'll quit
begging :)

Thanks!

"Bernie Deitrick" wrote:

Again, using Ctrl-Shift-Enter:

=(C9-SUM(IF(F9:AD9<"",VALUE(MID(F9:AD9,3,LEN(F9:AD9))) )))/2

Since you have a letter and a space, the text string is really two
characters long, so I changed the ,2, to ,3, in the MID function.

=(C9-SUM(IF(F9:AD9<"",VALUE(MID(F9:AD9,3,1)))))/2

would also work, since you only have a 2 or a 4 in the cell (a number string
one character long).

HTH,
Bernie
MS Excel MVP




"Aundria123" wrote in message
...
Bernie,

I hope you wont give up! I liked your formula and so I wanted to add that
when I click the "!" to see about the error, it looks to me that it gets
to

46/VALUE and breaks.

What does VALUE do? That seems to be the problem...

When I do EVALUATE FORMULA, it underlines and italicizes (#VALUE!<""

Then I click EVALUATE again and it underlines/italicizes

(IF( #VALUE!,VALUE(MID(F9:AD9,2,LEN(F9:AD9))))

I hit EVALUATE again

(46-#VALUE!)/2

I hit again
( #VALUE!)/2

I hit again

#VALUE!/2

I hit it for the last time and I only can RESTART

#VALUE!

Thanks for your help!

"Bernie Deitrick" wrote:

Aundria,

If you always have just one letter, then array enter (enter using
Ctrl-Shift-Enter) this formula

=(C9-SUM(IF(F9:AD9<"",VALUE(MID(F9:AD9,2,LEN(F9:AD9))) )))/2

HTH,
Bernie
MS Excel MVP


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