AGGREGATE & #NUM!

[Decryptix]

Good morning!

First off, love the forum, it's definitely helped me learn a fair bit and bash out a few errors that were plaguing a few spreadsheets I use on a regular basis.

Currently running into an issue with my latest project. I had a solution for Excel 365 using XLOOKUP, but due to the fact at work we use Excel 2013 and 2016 on different systems, I need to make sure the function I'm trying to duplicate is compatible with both; Enter AGGREGATE.

Goal: What I'm trying to do is have the aggregate function compare several criteria (Employment Class, Pay Rate, Allowance Type 1, Allowance Type 2) and spit out a new Pay Rate based on the individuals employment Class (of which there are 3).

Issue: The aggregate function works perfect, until we get to individuals whose calculated total of Pay & Allowances is more than the new pay scales. This means they need be slotted at the Max pay rate, and may drop slightly in pay. In these situations AGGREGATE function spits out a #NUM! error. I've tried adjusting the function and options, but I'm wondering if it needs to be adjusted to include an IF clause... The D2 value listed in the function below is the members employment class as well which determines whether there's 14 or 17 pay levels the member can have. No individual can receive more than the max.

I can provide a copy of the info after work via XLBB if required, but just wondering if there's a quick solution.

=AGGREGATE(15,6,Lists!X2:X307/((Lists!V2:V307=C2)*(Lists!W2:W307=D2)*(Lists!Y2:Y307>K2)),1)

 

[Decryptix]

Does this need to work in 2013 & 2016 or just 365?

2013 & 2016. If we utilized 365, I could just use XLookup and get it to work fine, as I already have a version - but the powers that be want this to work on systems that we already have in place.

 

[Fluff]

Ok, how about

=IF(D2="","",IFERROR(AGGREGATE(15,6,Lists!X2:X308/((Lists!V2:V308=C2)*(Lists!W2:W308=D2)*(Lists!Y2:Y308>K2)),1),IF(D2="Biotech",14,17)))

 

[Decryptix]

Ok, how about

=IF(D2="","",IFERROR(AGGREGATE(15,6,Lists!X2:X308/((Lists!V2:V308=C2)*(Lists!W2:W308=D2)*(Lists!Y2:Y308>K2)),1),IF(D2="Biotech",14,17)))

That's it! Works! Thank you so much!

Out of curiosity, I think I understand the placement of the IF, but I'm not quite understanding the use of D2="",""... I get that after the second set of quotations it's covering off the value if false aspect of the If Command, just the stuff prior isn't 100% clear to me.

 

[Fluff]

That is to prevent the aggregate from returning an error if D2 is empty, otherwise those rows would show 17

 

[Decryptix]

Excellent, thanks again! This will only further assist in my push to get us switched to 365.

 

[Fluff]

You're welcome & thanks for the feedback.