Min Fuction

[Justinmih]

Hi there,
I am using the Min function below:-

Please Login or Register  to view this content.

Rather than using C9:Q9 i need it to use C9:G9,I9:L9,N9:Q9 however when i use it, it says I have used to many arguments for this function, is there anyway round this?

I have been stumped on this for ages, if anyone could help it would be much apprecaited.

Thanks

Justin.

[Pepe Le Mokko]

=small(choose({1,2,3},(C9:G9,I9:L9,N9:Q9),1) entered as an array formula works provided there are no error values

[Justinmih]

Hi, Thanks for getting back to me so quickly.
Whan i use it, it says my formula is missing a parenthesis?

[Tony Valko]

The ranges C9:G9, I9:L9, N9:Q9 are separated by 2 cells, H9 and M9. What's in those 2 cells? Are there numbers in those cells?

[Justinmih]

Hi Tony,

Its a total of the previous numbers.
Its actually, Wk1,2,3,4,5 etc then a subtotal of each.

Cheers

[shg]

=small((c9:g9, i9:l9, n9:q9), 1)

[Tony Valko]

Well, if you have to avoid #N/A errors and zero values then you'll have to do something like this...

Array entered**:

=MIN(IF(ISNUMBER(C9:G9),IF(C9:G9>0,C9:G9)),IF(ISNUMBER(I9:L9),IF(I9:L9>0,I9:L9)),IF(ISNUMBER(N9:Q9),IF(N9:Q9>0,N9:Q9)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If each group of numbers represents a week that formula would be very long if you have many weeks to evaluate. The above formula is only evaluating 3 weeks so far.

You may want to consider a redesign of the data structure!

[Tony Valko]

Normally, MIN will work:

=MIN(C9:G9,I9:L9,N9:Q9)

But, in this case I think we have to account for #N/A errors and zeros.

[shg]

Or fix problems at the source.

[Justinmih]

HI Guy's
Thanks to you all for replying so quickly.

Tony the following code worked, thank you so much.

Please Login or Register  to view this content.

[Tony Valko]

You're welcome. Thanks for the feedback!