Still trying to find the "MAX"

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Bob,
It does work when using the same format of the cells ie..
2 wks, however some of my cells contain "stk", "stock"
and 0 (zero) and I need to keep these here. Is there a
way to tell the formula to ignore anything with the 0 or
the letters stk?

Thanks for your help in getting me this far.
Joe
-----Original Message-----
Joe,

As long as all cells are entered, and all are of the same format, try this

=MAX(VALUE(LEFT(S5:S16,FIND(" ",S5:S16)-1)))

it's an array formula, so you need to use Ctrl-Shift- Enter to complete the
input.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
..
 
=MAX(IF(ISERROR(FIND("wks",S5:S16)),0,VALUE(LEFT(S5:S16,FIND("
",S5:S16)-1))))

Entered with Ctrl+Shift+Enter rather than just enter since it is an array
formula

as long as you don't have any cells with just wks and no number with it.
 
=MAX(IF(ISERROR(FIND("wks",S5:S16)),0,
VALUE(LEFT(S5:S16,FIND(" ",S5:S16)-1))))
Click to expand...
...

Quibble: unless there's a possibility Transition Formula Evaluation could be
enabled, VALUE need never be used. --X and VALUE(X) will always give the same
result when Transition Formula Evaluation is disabled (i.e., standard Excel
formula evaluation rather than Lotus 123R2-like evaluation). Not an issue here,
but VALUE should be avoided in more deeply nested expressions.
 
Back
Top