Array formula with MIN: too many arguments

[mikmonto]

Hi,

I have this array formula:

=MIN(IF('Review Calendar'!$B$2:$B$9999=Analysis!A31,'Review Calendar'!$D$2:$D$9999,'Review Calendar'!$G$2:$G$9999=TRUE))

which checks the minimum date contained in sheet "Review Calendar", column D, being the Column G set to TRUE for a certain Client (contained in Cell Analysis!A31).

So far so good. But if I try to add another parameter ('Review Calendar'!$H$2:$H$9999=$B$1)

=MIN(IF('Review Calendar'!$B$2:$B$9999=Analysis!A31,'Review Calendar'!$D$2:$D$9999,'Review Calendar'!$G$2:$G$9999=TRUE,'Review Calendar'!$H$2:$H$9999=$B$1))

The array formula errors out with "Too many parameters" error.

I totally do not get what is wrong with this, do you have any idea?

Thanks,
M

[wenqq3]

Maybe this:
Function if only 3 argument.
IF( condition, [value_if_true], [value_if_false] )
=MIN(IF('Review Calendar'!$B$2:$B$9999=Analysis!A31,'Review Calendar'!$D$2:$D$9999,'Review Calendar'!$G$2:$G$9999=TRUE,'Review Calendar'!$H$2:$H$9999=$B$1))

There is 4 inside your if function.

[Pete_UK]

Maybe better to write it like this:

=MIN(IF(('Review Calendar'!$B$2:$B$9999=Analysis!A31)*('Review Calendar'!$G$2:$G$9999=TRUE),'Review Calendar'!$D$2:$D$9999))

where * is equivalent to AND in array formulae, and you don't need the FALSE element of the IF function.

So then you can add your other term(s) like this:

=MIN(IF(('Review Calendar'!$B$2:$B$9999=Analysis!A31)*('Review Calendar'!$G$2:$G$9999=TRUE)*('Review Calendar'!$H$2:$H$9999=$B$1),'Review Calendar'!$D$2:$D$9999))

Note that you need to commit the formula using Ctrl-Shift-Enter.

Hope this helps.

Pete

[mikmonto]

Great ...

So what do you think can be a possible solution?
I tried this to find out the MAX value

=MAX(('Review Calendar'!$B$2:$B$9999=A31)*('Review Calendar'!$D$2:$D$9999)*('Review Calendar'!$G$2:$G$9999=TRUE)*('Review Calendar'!$H$2:$H$9999=$B$1))

which use to work, but now is broken. Anyways it was always returning a 0 (00/01/1900)

[mikmonto]

Thanks Pete,

It worked perfectly (even with MAX function).
How would you combine it with SMALL/LARGE to get the 2nd min/max?

[Pete_UK]

You need to do it like this:

=MAX(IF(conditions,max_range))

so try it like this:

=MAX(IF(('Review Calendar'!$B$2:$B$9999=A31)*('Review Calendar'!$G$2:$G$9999=TRUE)*('Review Calendar'!$H$2:$H$9999=$B$1),'Review Calendar'!$D$2:$D$9999))

Hope this helps.

Pete