ARRAY formula

[orsm6]

Hi all, on a spreadsheet I would like to list the top ten values of a list of items using the LARGE function and sets of criteria, I entered this formula in and it works, but when I try to expand it by adding another 3 or more criteria it doesn't work, any suggestions please on how I can add extra criteria to make it work??

{=IFERROR(LARGE(IF(('sap dump 2'!C:C>="PA000000")*('sap dump 2'!C:C<"PA100000"),'sap dump 2'!G:G),ROW(A1)),"")}

I want to add more criteria that may look like this: ('sap dump 2'!C:C>="20000000")*('sap dump 2'!C:C<"30000000"),

thanks in advance

EDIT: When I say it works I mean that it will list the top ten with that criteria, but because I cant expand the criteria I am missing data

 

[jarjarbingie]

Hi all, on a spreadsheet I would like to list the top ten values of a list of items using the LARGE function and sets of criteria, I entered this formula in and it works, but when I try to expand it by adding another 3 or more criteria it doesn't work, any suggestions please on how I can add extra criteria to make it work??

{=IFERROR(LARGE(IF(('sap dump 2'!C:C>="PA000000")*('sap dump 2'!C:C<"PA100000"),'sap dump 2'!G:G),ROW(A1)),"")}

I want to add more criteria that may look like this: ('sap dump 2'!C:C>="20000000")*('sap dump 2'!C:C<"30000000"),

thanks in advance

EDIT: When I say it works I mean that it will list the top ten with that criteria, but because I cant expand the criteria I am missing data

=IFERROR(LARGE(IF(('sap dump 2'!C:C>="PA000000")*('sap dump 2'!C:C<"PA100000")*('sap dump 2'!C:C>=20000000)*('sap dump 2'!C:C<30000000),'sap dump 2'!G:G),ROW(A1)),"")

 

[orsm6]

Hi jarjar... thank you for the reply. I tried this formula but it returns a blank cell. (tried as an array and as a normal formula)


thanks.

 

[FDibbins]

1st, try and avoid using full-column references in an array formula, it could slow your file down

2nd, testing for multiple criteria to the same column can be tricky but not impossible), but looking at your 1st and 2nd formulas, something does not look right.
you are testing for (sap dump 2'!C:C<"PA100000") which implies that C contains text?
But then you are looking to test for within a value range in the same column? 'sap dump 2'!C:C<"PA100000")

So does C contain text or value?

I would probably do this with a helper column to ID which rows to consider

 

[orsm6]

Hi FDibbins.... Yes column C has text and it has numbers.

I think I know what you mean by helper column, i'll give that a go. Appreciate your response, thanks.

 

[FDibbins]

Hi FDibbins.... Yes column C has text and it has numbers.

I think I know what you mean by helper column, i'll give that a go. Appreciate your response, thanks.

OK then excel will have a hard time with that test, it is either testing for a text string OR a value range, it wont be able to do both, I think a helper column will serve you better.

=if(or(C1="string",c1>min,C1<max),G1,0)

 

[orsm6]

not following your formula, what is the 'string' and what part of the formula returns the value I am looking for?

in my helper column (formula cell F2) look at cell C2 and if these criteria met return PAC, or RAW ...

criteria:

greater than less than
PA00000 PA10000
RA00000 RA70000
10000000 20000000

<ra70000,><pa10000
<pa100000 <pa100000
<ra10000
<ra10000 <ra700000
20000000 300000000

if column D contains the word "REWORK" then cell F2 would return REW</ra10000></ra10000
</pa100000></pa10000
</ra70000,></pa10000,>

 

[FDibbins]

OK, just to make sure I understand you here.

Are you saying you have "values" in the range of PA00000 to PA10000?

If so, those are not values, they are text, and trying to find if something falls within that "range", is lake asking excel if cat falls within tree and fish.

 

[orsm6]

Column C is formatted as "general"... not by number or text

Column C contains lots of different values, they could be anywhere between
PA00000 and PA10000
RA00000 and RA70000
10000000 and 20000000
20000000 and 30000000


so yes, there is text and there is numbers, but all of the column is formatted as General....if that helps. I can do a SUMIFS formula which works, so not sure why I can't do what I need as above in the other post... =SUM(SUMIFS(G:G,C:C,">=20000000",C:C,"<30000000")+SUMIFS(G:G,C:C,">=PA000000",C:C,"<=PA100000"))/1000000

 

[FDibbins]

doesn't matter how it is formatted, if you have any text in a cell, even if there are numbers as well, that cell is text. If you have PA00000 in a cell, that is text