help adding to my formula

[Guest]

the following formula is what i need help with it has most
of the properties of the formula below it but im trying to
add this part of the formula -SUM($C$5E7),0) can someone
help me fix it
=MAX(MIN(SUM(IF(ISTEXT(F5:F7),--LEFT(F5:F7,LEN(F5:F7)-
1),F5:F7)),40+SUM(IF(ISTEXT($C$7:$J$7),--LEFT($C$7:$J$7,LEN
($C$7:$J$7)-1),$C$7:$J$7))))


=MAX(MIN(SUM(F5:7,40+SUM($C$7$J$7)-SUM($C$5E7),0)

 

[Biff]

Hi!

If you need to add any more "components" to this and need
help in the future, I would suggest posting a large sample
of the data so that a less "complex" solution may be
attempted!

Entered as an array:

=MAX(MIN(SUM(IF(ISTEXT(F5:F7),--LEFT(F5:F7,LEN(F5:F7)-
1),F5:F7)),40+SUM(IF(ISTEXT($C$7:$J$7),--LEFT($C$7:$J$7,LEN
($C$7:$J$7)-1),$C$7:$J$7)-SUM(IF(ISTEXT(C5:E7),--LEFT
(C5:E7,LEN(C5:E7)-1),C5:E7)))),0)

Biff

 

[Guest]

I have been working at this for a while and i think im
using the easiest formula available for what i need to do.

the last section of this formula i would like to get the
-c7:d7 to be able to ignore text like the rest of the
formula. how do i do the " (istext, --Left, len( ) -1 i
have tried and can't get it right.





=MAX(MIN(SUM(IF(ISTEXT(D57),--LEFT(D57,LEN(D57)-
1),D57)),40+SUM(IF(ISTEXT($D$5:$D$7),--LEFT($D$5:$D$7,LEN
($D$5:$D$7)-1),$D$7:$D$7),-SUM(IF(ISTEXT(C57),--LEFT
(C57,LEN(C57)-1),C57-C77)))),0)

 

[Guest]

i went into cell format and tried to format the cell to
read numbers and one letter example 10B. this would work
great if i could make it not a specific letter becuase the
letter will sometimes be V or T and sometimes not be a
letter. Is there a way to format the cell that way?

 

[Biff]

Hi!

Are you going to be able to figure out what this formula
does 6 months from now?

=MAX(MIN(SUM(IF(ISTEXT(D57),--LEFT(D57,LEN(D57)-
1),D57)),40+SUM(IF(ISTEXT($D$5:$D$7),--LEFT($D$5:$D$7,LEN
($D$5:$D$7)-1),$D$7:$D$7),-SUM(IF(ISTEXT(C57),--LEFT
(C57,LEN(C57)-1),C57),SUM(IF(ISTEXT(C77),--LEFT
(C77,LEN(C77)-1),C77))))),0)

Helper cells would make this so much easier!

Biff

 

[Guest]

Biff thanks for your help but something isn't rigt with
the layout of the formula. and i don't know how to explain
it to you with out you seeing what im working with can i
email you a small section of my spreadsheet so maybe we
can figure out the best solution.