writing a formula

[Guest]

my spread sheet is complete but my boss threw a monkey
wrench into it and now back to the drawing board.


this is what i am working with. the way i understand this
is that in this cell it is adding up c5:c7 and the sum
will not exceed 40.
C8=MIN(SUM(c5:c7),40+SUM($C$7:$J$7))

that was good but now i need to be able to sometimes have
a letter to the right of the numbers in cells c5:c7 so now
im using this and it is working fine but i don't
understand it and if i don't understand i can't write
others
C8=MAX(MIN(SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7,LEN(C5:C7)-
1),C5:C7)),40+SUM(IF(ISTEXT($C$7:$J$7),--LEFT($C$7:$J$7,LEN
($C$7:$J$7)-1),$C$7:$J$7))))

now im trying to write a formula that can ignore the text
that may be in the cells sometimes.
C10=SUM(c5:c7)-c8

 

[Biff]

Hi!

Hey, I remember seeing this in a previous post!

Ok, here goes!

this is what i am working with. the way i understand this
is that in this cell it is adding up c5:c7 and the sum
will not exceed 40.
C8=MIN(SUM(c5:c7),40+SUM($C$7:$J$7))

Well, not exactly. It's returning the MINIMUM value that
is either the SUM of C5:C7 *OR* 40+ the SUM of C7:J7,
which ever is lower.

that was good but now i need to be able to sometimes have
a letter to the right of the numbers in cells c5:c7 so
now im using this and it is working fine but i don't
understand it and if i don't understand i can't write
others
C8=MAX(MIN(SUM(IF(ISTEXT(C5:C7),--LEFT(C5:C7,LEN(C5:C7)-
1),C5:C7)),40+SUM(IF(ISTEXT($C$7:$J$7),--LEFT

($C$7:$J$7,LEN($C$7:$J$7)-1),$C$7:$J$7))))

This one's a piece of cake! <g>

First off, the MAX is doing nothing at all. This is an
array formula. It's testing the values in C5:C7 and C7:J7
to see if they contain text, eg: 10T. If they do contain
text, the LEN function will compare the lenght of the
string eg:10T and "eliminate" one character (that's what
the -1 is for) and pass only the "10" to the SUM function.
The "--" is used to convert text numbers to numeric
numbers so they can be used in the SUM function.

If either range C5:C7 or C7:J7 does not contain text, then
this portion of the formula is bypassed because the ISTEXT
function will evaluate to FALSE.
--LEFT(.........


Now, it's the same as: It's returning the MINIMUM value
that is either the SUM of C5:C7 *OR* 40+ the SUM of C7:J7,
which ever is lower. The MAX function is doing nothing and
is not needed

I hope that helped you. It can be confusing!

Biff