Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

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!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 61
Default How to SUM only numbers in a range with Text?

="t " &text(C9-(SUM(F9:AD9))/2,"####")



"Aundria123" wrote:

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!

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default How to SUM only numbers in a range with Text?

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!



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default How to SUM only numbers in a range with Text?

Tell him he is an idiot! Use an extra column for any text entries


--


Regards,


Peo Sjoblom




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



  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 8,651
Default How to SUM only numbers in a range with Text?

One option is to produce a set of helper columns, so from "T 2" in F9 you
could extract the 2 by =RIGHT(F9), and copy across appropriately. Use the
new columns in your SUM (and hide the new columns if you want to).
--
David Biddulph

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





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

Hello,

Thank you for your reply. When I enter this, it just puts the letter t in
front of my UNUSED UNITs #, i.e. t 41.

But I need to be able to enter either t or e throughout the whole row. And
I don't need the letter to be in the UNUSED UNITs column, only the number.

I hope this makes sense. Thanks!

"jhyatt" wrote:

="t " &text(C9-(SUM(F9:AD9))/2,"####")



"Aundria123" wrote:

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!

  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

Bernie,

I'm not sure if I'm doing the CTRL+SHIFT+ENTER right. Can you dumb it down
more for me? When I try this, nothing gets entered into the cell.

Right now I do a COPY/PASTE of the formula into the CELL where UNUSED UNITs
are calculated. I see "####" indicating there is a error somewhere.

It will always be 1 letter and 1 number, i.e. T 4 OR E 2. There is a space
between the letter and the number.

Thank you 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!




  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 61
Default How to SUM only numbers in a range with Text?

you could do this the only problem i have come up with is if any of the cells
f9:ad9 are blank it = an ERROR

=C9-MID(F9,3,6)-MID(G9,3,6)-MID(H9,3,6)-MID(I9,3,6)-MID(J9,3,6)-MID(K9,3,6)-MID(L9,3,6)-MID(M9,3,6)-MID(N9,3,6)-MID(O9,3,6)-MID(P9,3,6)-MID(Q9,3,6)-MID(R9,3,6)-MID(S9,3,6)-MID(T9,3,6)-MID(U9,3,6)-MID(V9,3,6)-MID(V9,3,6)-MID(X9,3,6)-MID(Y9,3,6)-MID(Z9,3,6)-MID(AA9,3,6)-MID(AB9,3,6)-MID(AC9,3,6)-MID(AD9,3,6)

"Aundria123" wrote:

Hello,

Thank you for your reply. When I enter this, it just puts the letter t in
front of my UNUSED UNITs #, i.e. t 41.

But I need to be able to enter either t or e throughout the whole row. And
I don't need the letter to be in the UNUSED UNITs column, only the number.

I hope this makes sense. Thanks!

"jhyatt" wrote:

="t " &text(C9-(SUM(F9:AD9))/2,"####")



"Aundria123" wrote:

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!

  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22
Default How to SUM only numbers in a range with Text?

Peo,

nice joke !
I suggest that its better to *firstly* persuade her boss to use the extra
column, otherwise if *the bossy symptoms persist*, i suggest that she ask her
boss to do it himself.

we're excellers to reduce idiots <g!

"Peo Sjoblom" wrote:

Tell him he is an idiot! Use an extra column for any text entries


--


Regards,


Peo Sjoblom




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




  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

David,

I did what you suggested. I started with F42 to AD42, which corresponds
with F9 to AD9. I have only numbers in my cell on the second page. I then
put the below formula in AE9 (UNUSED UNITS column) but now the value is
always the same as in C9 (TOTAL UNITS). It's like the formula is no long
doing the SUM / 2 part.

=(C9-(SUM(F42:AD42))/2)

What gives?

Thanks!

"David Biddulph" wrote:

One option is to produce a set of helper columns, so from "T 2" in F9 you
could extract the 2 by =RIGHT(F9), and copy across appropriately. Use the
new columns in your SUM (and hide the new columns if you want to).
--
David Biddulph

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






  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

Hey,

Thanks for trying. Some cells will be blank as we only enter a value when a
client has been seen. F9 - AD9 are the days of the month and I enter "T 4"
only when the client is seen.

Thanks!

"jhyatt" wrote:

you could do this the only problem i have come up with is if any of the cells
f9:ad9 are blank it = an ERROR

=C9-MID(F9,3,6)-MID(G9,3,6)-MID(H9,3,6)-MID(I9,3,6)-MID(J9,3,6)-MID(K9,3,6)-MID(L9,3,6)-MID(M9,3,6)-MID(N9,3,6)-MID(O9,3,6)-MID(P9,3,6)-MID(Q9,3,6)-MID(R9,3,6)-MID(S9,3,6)-MID(T9,3,6)-MID(U9,3,6)-MID(V9,3,6)-MID(V9,3,6)-MID(X9,3,6)-MID(Y9,3,6)-MID(Z9,3,6)-MID(AA9,3,6)-MID(AB9,3,6)-MID(AC9,3,6)-MID(AD9,3,6)

"Aundria123" wrote:

Hello,

Thank you for your reply. When I enter this, it just puts the letter t in
front of my UNUSED UNITs #, i.e. t 41.

But I need to be able to enter either t or e throughout the whole row. And
I don't need the letter to be in the UNUSED UNITs column, only the number.

I hope this makes sense. Thanks!

"jhyatt" wrote:

="t " &text(C9-(SUM(F9:AD9))/2,"####")



"Aundria123" wrote:

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!

  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

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!




  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,441
Default How to SUM only numbers in a range with Text?

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!






  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 860
Default How to SUM only numbers in a range with Text?

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!



  #15   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 8,651
Default How to SUM only numbers in a range with Text?

Sorry, I should have said =--RIGHT(F9), not =RIGHT(F9), as the latter
produces text, rather than a number. I apologise for causing you confusion.
--
David Biddulph

"Aundria123" wrote in message
...
David,

I did what you suggested. I started with F42 to AD42, which corresponds
with F9 to AD9. I have only numbers in my cell on the second page. I
then
put the below formula in AE9 (UNUSED UNITS column) but now the value is
always the same as in C9 (TOTAL UNITS). It's like the formula is no long
doing the SUM / 2 part.

=(C9-(SUM(F42:AD42))/2)

What gives?

Thanks!

"David Biddulph" wrote:

One option is to produce a set of helper columns, so from "T 2" in F9 you
could extract the 2 by =RIGHT(F9), and copy across appropriately. Use the
new columns in your SUM (and hide the new columns if you want to).
--
David Biddulph

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








  #16   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 860
Default How to SUM only numbers in a range with Text?

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!





  #17   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,345
Default How to SUM only numbers in a range with Text?

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








  #18   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 860
Default How to SUM only numbers in a range with Text?

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!









  #19   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,345
Default How to SUM only numbers in a range with Text?

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!












  #20   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 860
Default How to SUM only numbers in a range with Text?

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!
















  #21   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default How to SUM only numbers in a range with Text?

=(C9-SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(F9:AD9,"T ",""),"E ",""))))/2


will work as well


--


Regards,


Peo Sjoblom




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














  #22   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,345
Default How to SUM only numbers in a range with Text?

Hi Martin,

Seeing my response in print it looks a bit terse - I must have been
associating with Harlan too much <g

I did not mean to put your formula down, I was just in a bit of a rush when
I wrote it. Your formula does satisfy the OP's requirements as stated with
no double digit numbers given.

My apologies, it was not meant that way.

--
Regards,

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
...
OK, Thanks again Sandy.



  #23   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default How to SUM only numbers in a range with Text?

Hi Martin,

Seeing my response in print it looks a bit terse - I must have been
associating with Harlan too much <g

I did not mean to put your formula down, I was just in a bit of a rush
when I wrote it. Your formula does satisfy the OP's requirements as
stated with no double digit numbers given.

My apologies, it was not meant that way.



Aren't you supposed to be a "dour" Scot? <vbg


--


Regards,


Peo Sjoblom



  #24   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,345
Default How to SUM only numbers in a range with Text?

"Peo Sjoblom" wrote in message
...
Aren't you supposed to be a "dour" Scot? <vbg



LOL

Incidentally does anyone know what has happened to Harlan lately?

--

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


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


  #25   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default How to SUM only numbers in a range with Text?

"Sandy Mann" wrote in message
...
"Peo Sjoblom" wrote in message
...
Aren't you supposed to be a "dour" Scot? <vbg



LOL

Incidentally does anyone know what has happened to Harlan lately?

--



Vacation? Or maybe there is a convention for mathematically inclined
curmudgeons somewhere?
He was gone for some time and returned with a few posts but he seems to be
gone again, we have a saying in Sweden

"You don't miss the sow until the sty is empty"


Somehow he keeps other posters in line or they will be corrected <vbg


--


Regards,


Peo Sjoblom








  #26   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

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!






  #27   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

David,

Thanks for trying. This forumla only works if every sell in the range of
F9:AD9 has something in it. Some cells are empty and so I get a value error.

This works if every cell is populated but we only see the client 1-3 times a
week.

Any other ideas?

Thanks!

"David Biddulph" wrote:

Sorry, I should have said =--RIGHT(F9), not =RIGHT(F9), as the latter
produces text, rather than a number. I apologise for causing you confusion.
--
David Biddulph

"Aundria123" wrote in message
...
David,

I did what you suggested. I started with F42 to AD42, which corresponds
with F9 to AD9. I have only numbers in my cell on the second page. I
then
put the below formula in AE9 (UNUSED UNITS column) but now the value is
always the same as in C9 (TOTAL UNITS). It's like the formula is no long
doing the SUM / 2 part.

=(C9-(SUM(F42:AD42))/2)

What gives?

Thanks!

"David Biddulph" wrote:

One option is to produce a set of helper columns, so from "T 2" in F9 you
could extract the 2 by =RIGHT(F9), and copy across appropriately. Use the
new columns in your SUM (and hide the new columns if you want to).
--
David Biddulph

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






  #28   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default ANSWERED How to SUM only numbers in a range with Text?

Sandy,

You did it!! Thanks everyone for your help, especially you Sandy!

Best Regards,

Aundria

"Sandy Mann" wrote:

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








  #29   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default ANSWERED How to SUM only numbers in a range with Text?

Bernie,

I'm not sure why Sandy's worked and yours did not, but I'm thankful to you
for getting me on the right track!

Aundria

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






  #30   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,345
Default ANSWERED How to SUM only numbers in a range with Text?

Thanks for the thanks but it was Martin W's formula

How about that Martin? You won the race <g

--

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


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


"Aundria123" wrote in message
...
Sandy,

You did it!! Thanks everyone for your help, especially you Sandy!

Best Regards,

Aundria

"Sandy Mann" wrote:

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













  #31   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 860
Default ANSWERED How to SUM only numbers in a range with Text?

Hi Sandy,

It's not about the race
It's about the journey <g

I got as much from this thread as the OP.

P.S. Your earlier thread didn't sound terse or Harlanesque
to me. I'm an Aussie Sandy, you need to be trying very
hard to ruffle my feathers.

Thanks Again
Martin

"Sandy Mann" wrote in message
...
Thanks for the thanks but it was Martin W's formula

How about that Martin? You won the race <g

--

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


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


"Aundria123" wrote in message
...
Sandy,

You did it!! Thanks everyone for your help, especially you Sandy!

Best Regards,

Aundria

"Sandy Mann" wrote:

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













  #32   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default ANSWERED How to SUM only numbers in a range with Text?

Sandy,

Oh I was confused! Thanks Martin!!! I went back and put a YES for his POST
also. I hope this is what gives everyone a good rating.

You all are very helpful!

Aundria

"Sandy Mann" wrote:

Thanks for the thanks but it was Martin W's formula

How about that Martin? You won the race <g

--

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


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


"Aundria123" wrote in message
...
Sandy,

You did it!! Thanks everyone for your help, especially you Sandy!

Best Regards,

Aundria

"Sandy Mann" wrote:

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












  #33   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 8,651
Default How to SUM only numbers in a range with Text?

You could solve that by replacing the =--RIGHT(F9) formula by
=IF(F9="","",--RIGHT(F9))
--
David Biddulph

"Aundria123" wrote in message
...
David,

Thanks for trying. This forumla only works if every sell in the range of
F9:AD9 has something in it. Some cells are empty and so I get a value
error.

This works if every cell is populated but we only see the client 1-3 times
a
week.

Any other ideas?


"David Biddulph" wrote:

Sorry, I should have said =--RIGHT(F9), not =RIGHT(F9), as the latter
produces text, rather than a number. I apologise for causing you
confusion.
--
David Biddulph

"Aundria123" wrote in message
...
David,

I did what you suggested. I started with F42 to AD42, which
corresponds
with F9 to AD9. I have only numbers in my cell on the second page. I
then
put the below formula in AE9 (UNUSED UNITS column) but now the value is
always the same as in C9 (TOTAL UNITS). It's like the formula is no
long
doing the SUM / 2 part.

=(C9-(SUM(F42:AD42))/2)

What gives?

Thanks!

"David Biddulph" wrote:

One option is to produce a set of helper columns, so from "T 2" in F9
you
could extract the 2 by =RIGHT(F9), and copy across appropriately. Use
the
new columns in your SUM (and hide the new columns if you want to).
--
David Biddulph

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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to select top six numbers from a of range of random numbers Jack M Taylor Excel Worksheet Functions 4 January 30th 07 10:18 PM
How do I transform numbers (different ranges) to text (diff. range Ty Eaton Excel Worksheet Functions 1 December 13th 06 01:57 AM
two columns range of numbers need to list all numbers in the range arsovat New Users to Excel 2 October 30th 06 09:21 PM
split range of numbers in two columns to as many as numbers in ran arsovat Excel Discussion (Misc queries) 2 October 30th 06 04:57 PM
Count comma separated numbers, numbers in a range with dash, not t Mahendra Excel Discussion (Misc queries) 0 August 8th 05 05:56 PM


All times are GMT +1. The time now is 06:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"