Array formula use

[brianmock]

I have dates running horizontal across sheet in row 2 from B2 to Unknown
I have final calculation running horizontal across sheet in row 64 from B64 to unknown (same Row 2)
I have a calculation type flag running horizontal across row 65 from B65 to unknown
Calculation flags are Fixed < Calc < Projected but they vary in that fixed may be 4 months today and 3 tomorrow

I want to find the
(1) max fixed date
(2) min calc date
(3) min projected date
(4) max projected date

Suggestions??

 

[Somendra Misra]

Hi brianmock,

Can you just upload a workbook with dummy input and output, that will help us to understand your need in a better way.

Regards!

 

[brianmock]

The contents tab is where I want to find cells D5:E6
I have tried {=MAX((Widgets!2:2)*ISNUMBER(Widgets!28:28)*Widgets!29:29="Fixed")} in Contents!D5 to no avail

 

[Somendra Misra]

@brianmock

Can you please tell in words what will be the logic to get value in D5:E6. And also what will the output.

Regards!

 

[brianmock]

In D5 11/1/2013 in E5 12/1/2013 F5 is given
in D6 12/1/2013 in E6 1/1/2014 F6 is given

Logic for column D Max of Widgets!2:2 if isnumber(Widgets!28:28) and (Widgets!29:29 = "Fixed")
logic for column E Min of Widgets!2:2 if isnumber(Widgets!28:28) and (Widgets!29:29 <> "Fixed")

 

[brianmock]

I can get column D with
{=max((Widgets!B2:AA2)*(Widgets!29:29 ="Fixed"))}

 

[Somendra Misra]

@brianmock

In D5 use: =IF(ISNUMBER(Widgets!$B$28:$Q$28),MAX(IF((Widgets!$B$29:$Q$29="Fixed"),Widgets!$B$2:$Q$2))) enter with Ctrl+Shift+Enter

In D6 use: =IF(ISNUMBER(Gadgets!$B$28:$Q$28),MAX(IF((Gadgets!$B$29:$Q$29="Fixed"),Gadgets!$B$2:$Q$2))) enter with Ctrl+Shift+Enter

In E5 use: =IF(ISNUMBER(Widgets!$B$28:$Q$28),MIN(IF(Widgets!$B$29:$Q$29<>"Fixed",Widgets!$B$2:$Q$2))) enter with Ctrl+Shift+Enter

In E6 use: =IF(ISNUMBER(Gadgets!$B$28:$Q$28),MIN(IF(Gadgets!$B$29:$Q$29<>"Fixed",Gadgets!$B$2:$Q$2))) enter with Ctrl+Shift+Enter

Just advise if any issue.

Regards!