ARRAY formula

orsm6

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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 :)
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jarjarbingie

Well-known Member
Joined
Nov 15, 2012
Messages
607
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

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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,>
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

Active Member
Joined
Oct 3, 2012
Messages
277
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,816
Messages
5,446,664
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top