Need assistance with an aggregate/if combined formula

JaredNAU

New Member
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 365
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
Joined
Aug 18, 2015
Messages
11,812
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
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 365

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 365
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
Joined
Aug 18, 2015
Messages
11,812
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.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top