ignoring text?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

my spread sheet that i was using did not have text in the
cells with the numbers. Now im trying to change that so i
can have one letter in a cell with the number but the
letter does not need to be calculated in the formula.
example: C5= 10.3T C6=10.15S

my formula that im trying to change is

=INT(SUM(INT(C5:J5)+MOD(C5:J5,1)*100/60))+MOD(SUM(INT
(C5:J5)+MOD(C5:J5,1)*100/60),1)*0.6
 
Hi,

Assuming that a cell will only have one letter, and is the last
character in the string, try:

=INT(SUM(INT(--LEFT(C5:J5,LEN(C5:J5)-1))+MOD(--LEFT(C5:J5,LEN(C5:J5)-1),1
)*100/60))+MOD(SUM(INT(--LEFT(C5:J5,LEN(C5:J5)-1))+MOD(--LEFT(C5:J5,LEN(C
5:J5)-1),1)*100/60),1)*0.6

*Note: Not tested
 
my spread sheet that i was using did not have text in the
cells with the numbers. Now im trying to change that so i
can have one letter in a cell with the number but the
letter does not need to be calculated in the formula.
example: C5= 10.3T C6=10.15S

my formula that im trying to change is

=INT(SUM(INT(C5:J5)+MOD(C5:J5,1)*100/60))
+MOD(SUM(INT(C5:J5)+MOD(C5:J5,1)*100/60),1)*0.6
Click to expand...

Mixing text and numerals in cells you want to handle as numbers in
downstream calculations is a very bad design. What are you trying to
accomplish by doing this? There may be better ways to do this.

However, if all cells have a single letter at the end of the cell contents,
then replace C5:J5 with LEFT(C5:J5,LEN(C5:J5)-1).
 
=MAX(MIN(SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7-C8,LEN(C5:C7)-
1),C5+C6+C7-C8))))

the above formula works if c5:c7 has text in the cell but
when they don't have text i get a "value" answer in the
cell why and can someone help me fix it
 
Hi,

Here are a few thoughts:

1) The arrays in SUM(...) are not the same size. I'm not sure why -C8
is included, but if you remove it, the formula will work.

=SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7,LEN(C5:C7)-
1)))

2) The MIN function is trying to determine the minimum value between the
results returned from SUM(...) and the sum of cells (C5+C6+C7-C8) which
contain text. Adding cells that contain text will give you a #VALUE
error.

3) The MAX function only has one argument, that is, the result of both
the MIN and SUM functions. So, either you need to provide more than one
argument for the function, or remove it since, as is, the function is
not necessary.

If you provide more details and what it is you're trying to accomplish,
someone may be able to help with a solution.
 
Back
Top