Array, #VALUE1,

[Alleemq]

Hi All,

I have an issue that I am unable to shake off. I require an array to give me the earliest maturity date based on two criteria. Going forward, I also want to be able to get 2nd earlist maturity and 3rd earliest maturity.

[TABLE="width: 339"]
<tbody>[TR]
[TD]Entity Name[/TD]
[TD]Pay or Receive[/TD]
[TD]Maturity date[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]RECEIVE[/TD]
[TD]6/11/2017[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]RECEIVE[/TD]
[TD]27/11/2017[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]RECEIVE[/TD]
[TD]27/12/2017[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]RECEIVE[/TD]
[TD]16/01/2018[/TD]
[/TR]
</tbody>[/TABLE]

I've played with a few prototypes below:

[TABLE="width: 626"]
<tbody>[TR]
[TD][TABLE="width: 738"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]6/11/2017[/TD]
[TD]+SMALL($H$114:$H$118,1)[/TD]
[/TR]
[TR]
[TD]6/11/2017[/TD]
[TD]{=SMALL(IF(G114:G118="RECEIVE",H114:H118,"OOOO"),1)}[/TD]
[/TR]
[TR]
[TD]6/11/2017[/TD]
[TD]{=SMALL(IF(F114:F118="APPLE",H114:H118,"OOOO"),1)}[/TD]
[/TR]
[TR]
[TD]#VALUE![/TD]
[TD]{=SMALL(IF(AND(G114:G118="RECEIVE",F114:F118="APPLE"),H114:H118,"OOOO"),1)}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

If I use one cirteria only, it works fine. I need to have two criterias as my bigger file have multiple columns, and the last value/formula is where I am stuck.
Also, though the current formula says 114-118 (which includes blanks); my bigger files will be formulae changed to columns eg G114:G118 will become G:G and this will obviously have blanks .

Any help/advise on this will be appreciated.

Thank you

AQ

 

[Faysal Farooqui]

Hi, first of all, while you are using an Array formula, you have to avoid AND() OR() function and use * for AND and + for OR.

 

[FormR]

[TABLE="width: 626"]
<tbody>[TR]
[TD][TABLE="width: 738"]
<tbody>[TR]
[TD]#VALUE![/TD]
[TD]{=SMALL(IF(AND(G114:G118="RECEIVE",F114:F118="APPLE"),H114:H118,"OOOO"),1)}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi, try like this:

{=SMALL(IF(G114:G118="RECEIVE",IF(F114:F118="APPLE",H114:H118)),1)}

 

[istiasztalos]

Hi,

As noted by Faysal you can combine several criteria for the IF function (as many as you want) with multiplication. The product of boolean variables will only be TRUE if all multiplied elements are TRUE.

This may be preferable to nesting IF formulas because you can more conveniently use any number of criteria, including one for example to only consider non-blanks, etc...

{=SMALL(IF((A:A="APPLE")*(B:B="RECEIVE")*(A:A<>""),C:C),1)} --- just continue listing criteria by adding statements in ()

 

[FormR]

This may be preferable to nesting IF formulas because you can more conveniently use any number of criteria

Hi, just a note - as I understand it, nesting is the more efficient method, especially as the number of criteria increases (if such things are important).

Also full column references should be widely avoided with either of these type of formula.

 

[Alleemq]

Thank you, one and all.

Its very much appreciated.

AQ