replace array formula

[andycreber]

hi

I want to place this array formula as it is too slow to process.

please could you help me find an alternative solution formula



{=MIN(IF($P$2:$P$50000=P3,IF($U$2:$U$50000<>"Fuel Card",$AM$2:$AM$50000)))}

Many thanks

 

[shift-del]

Andy,

try this:

[COLOR=#000000][FONT=Arial]=MINIFS($AM$2:$AM$50000,$P$2:$P$50000,P3,$U$2:$U$50000,"<>Fuel Card")[/FONT][/COLOR]

 

[andycreber]

hi

did not work, it does not like the "<>Fuel Card" part.

 

[FDibbins]

Depending on the version of excel you are using, minifs() may not be available to you

Do you really have 50 000 rows?
Another option might be to use a helper column to pull out the values you need to test?
=if(and(P2=P3,U2<>"Fuel Card"),AM2,"")
copied down, then find the MIN of that column.

 

[andycreber]

Hi

I am using excel 2013

I do have 50000 rows unfortunately.


that last formula did not work either, sorry.

appreciate your help but I'm still stuck

 

[Marcelo Branco]

Maybe DMIN function
https://support.office.com/en-us/article/DMIN-function-4AE6F1D9-1F26-40F1-A783-6DC3680192A3

M.

 

[andycreber]

Thought I would list the columns for help

u2 = text contains Fuel Card or ****
am2 contains expected end date

 

[andycreber]

p2 contains a range of numbers that can have multiple duplicates

 

[FDibbins]

that last formula did not work either, sorry.

What didn't work?
wrong answer?
no answer?
something else?

What exactly did you do?
[Table="width:, class:grid"][tr][td] [/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

2​

[/td][td]aa[/td][td]

10​

[/td][td][/td][td]bb[/td][td]

20​

[/td][td]regular MIN[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]

20​

[/td][td]

20​

[/td][td][/td][td]

20​

[/td][td]array MIN[/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td]

30​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]aa[/td][td]

40​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]cc[/td][td]

50​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]bb[/td][td]

60​

[/td][td]

60​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
Helper column
=IF(F2=$I$2,G2,"")
copied down

J2=MIN(H2:H6)
J3=MIN(IF($F$2:$F$7=I2,$G$2:$G$7))

 

[thisoldman]

Another possibility. I don't think this will be any faster, but it's entered as a regular formula.

=AGGREGATE(15, 6, $AM$2:$AM$50000 / (($P$2:$P$50000=P3) * ($U$2:$U$50000<>"Fuel Card")), 1)

15 is the AGGREGATE version of SMALL, 6 ignores errors, 1 tells it to look for the first smallest.