# Need assistance with an aggregate/if combined formula

#### JaredNAU

##### New Member
Good morning. First, I want to express how grateful I am in having this resource. I refer to it often and it has been a lifesaver. Now on to problem: I need assistance building an Excel formula. Here is the situation:

I am using a column (Q) that has priority numbers 1 through 5. I have another column (L) that has general numbers. I need to build two formulas that 1) evaluates the priority number and then 2) finds two max values based on whether or not the priority number is greater than 2 or less than 3. So, there will be two separate formulas. I am using aggregate since there may be errors in some cells. Here is what I have so far that isn't working and producing a #VALUE! error:

The formula for finding max value for priority numbers greater than 2: =AGGREGATE(4,6,(IF(Q:Q>2,L:L)))
The formula for finding max value for priority numbers less than 3: =AGGREGATE(4,6,(IF(Q:Q<3,L:L)))

I have tried combinations of this formula to no avail. It seems based on the error that I am using two different data types but both columns Q and L are set to the "general" type. I appreciate any guidance.

Thanks

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

Is this what you want?

Book1
HIJKLMNOPQ
1Max over 2Max under 3GeneralPriority
29098982
38795903
4511
5552
6383
7951
8725
9174
10773
11412
12454
13135
14834
15105
16912
17664
18873
19432
20692
21251
22
Sheet1
Cell Formulas
RangeFormula
H2:H3H2=AGGREGATE(14,6,\$L\$2:\$L\$21/(\$Q\$2:\$Q\$21>2),ROWS(\$H\$2:\$H2))
I2:I3I2=AGGREGATE(14,6,\$L\$2:\$L\$21/(\$Q\$2:\$Q\$21<3),ROWS(\$H\$2:\$H2))

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
You cannot use arrays when the 1st argument is less then 14, try this instead
Excel Formula:
=AGGREGATE(14,6,L2:L100/(Q2:Q100>2),1)

#### jasonb75

##### Well-known Member
Try
Excel Formula:
=AGGREGATE(14,6,IF(Q:Q>2,L:L),1)
Excel Formula:
=AGGREGATE(14,6,IF(Q:Q<3,L:L),1)
You can only use arrays in aggregate with function numbers that accept the optional k value at the end. All other functions will only accept ranges.

2 things to note.
1- an empty cell is less then 3, so it may cause erroneous results in Q has a number and L doesn't.
2- entire columns in array formulas will be resource hungry, try to limit your ranges to sensible sizes. If you have less than 1k rows of data then use of full columns is wasting more than 99.9% of the processing effort. Empty cells still have to be processed if they are included in the formula range.

#### JaredNAU

##### New Member

Thank you everyone. It looks like Fluff's solution works. Eric, your solution produced the same correct results but I wasn't sure why the ROWS function was needed and when I removed that part of the formula, it no longer worked.

#### jasonb75

##### Well-known Member
The ROWS() part in @Eric W's suggestion is a counter, if there are multiple valid results for the given criteria then filling the formula down the sheet will return those results.
As you can see from the visual example in post 2, the formula in H2 returns the MAX value, the formula in H3 returns the next largest value that meets the criteria of priority >2

#### JaredNAU

##### New Member
The ROWS() part in @Eric W's suggestion is a counter, if there are multiple valid results for the given criteria then filling the formula down the sheet will return those results.
As you can see from the visual example in post 2, the formula in H2 returns the MAX value, the formula in H3 returns the next largest value that meets the criteria of priority >2
Ok, that makes sense. Thanks!

#### Eric W

##### MrExcel MVP
Thank you everyone. It looks like Fluff's solution works. Eric, your solution produced the same correct results but I wasn't sure why the ROWS function was needed and when I removed that part of the formula, it no longer worked.
The ROWS function is used as a counter. In row 2, the ROWS(\$H\$2:\$H2) function returns a 1. In row 3, the ROWS(\$H\$2:\$H3) returns a 2. So the formula in row 2 returns the highest value, and the formula in row 3 returns the 2nd highest value. I did this because you said you wanted the 2 max values. You can of course use Fluff's formula, just change the 1 to a 2 for the 2nd highest value, but this way you only have one formula that you can drag down. So if you want to see more than 2 values, you just continue to copy down the formula. Whereas with Fluff's formula, you'd have to manually change each one. It just depends on your particular requirements.

Replies
1
Views
328
Replies
2
Views
137
Replies
6
Views
288
Replies
0
Views
131
Replies
1
Views
316

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,589
Messages
5,765,308
Members
425,272
Latest member
Umba

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back