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)

 

[Fluff]

Hi & welcome to MrExcel.
Maybe

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

 

[Decryptix]

Hi & welcome to MrExcel.
Maybe

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

Well, that definitely removes the error, but populates a 17 for those individuals that should be at 14. I'll post the spreadsheet using XL2BB after work. Getting closer and definitely appreciate the speedy response!

 

[Fluff]

I'll post the spreadsheet using XL2BB after work.

Ok

 

[Decryptix]

Here we go, I'm not sure how well this will come across, especially as all the values associated with the calculations are on a separate worksheet.

Essentially One class of engineer gets extra allowances for their field work, but only have 14 scales of pay per grade (annual pay raise). The other two, Biomedical and Chemical have 17 scales of pay, but it's not as much as the BioTech.

Not sure if this snippet is sufficient, but I've been trying to wrap my head around this for four days now, and this is where I'm stuck! If we used 365, it would be as simple as an XLOOKUP, but alas.

I appreciate the help so far. The modified version you posted was good, but for the BioTech it returned a value of 17 when their max is 14. It's a bit confusing I can imagine!

New Pay Calculation - Engineers.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee ID	Surname	Grade	Specialty	Curr PI	Curr Pay	Cat Pts	Cat Rate	Field Pts	Field Rate	Total Curr Pay	Entry Date to Curr Grade	TIG (Years)	New PI (Pay & Allw)	New PI (TIG)	Implemented PI	New Rate	Gain/Loss	Completed?
2						$0.00		$1,355.00		$0.00	$1,355.00		121	#NUM!	120	#NUM!	#NUM!	#NUM!	No
3						$0.00		$1,355.00		$0.00	$1,355.00		121	#NUM!	120	#NUM!	#NUM!	#NUM!	No
4						$0.00		$1,355.00		$0.00	$1,355.00		121	#NUM!	120	#NUM!	#NUM!	#NUM!	No
Calculator

Cell Formulas
Range	Formula
J2:J4	J2	=IF(D2="BioTech",(SUMIFS(Table28[Field Val],Table28[Field Pts],I2)),0)
K2:K4	K2	=F2+H2+J2
M2:M4	M2	=YEARFRAC(L2,"31-Mar-21")
N2:N4	N2	=AGGREGATE(15,6,Lists!X2:X308/((Lists!V2:V308=C2)*(Lists!W2:W308=D2)*(Lists!Y2:Y308>K2)),1)
O2:O4	O2	=IF(M2-1<1,"BASIC",M2-1)
P2:P4	P2	=INT(MAX(N2,O2))
Q2:Q4	Q2	=AGGREGATE(15,6,Lists!Y2:Y212/((Lists!V2:V212=C2)*(Lists!W2:W212=D2)*(Lists!Y2:Y212>K2)),1)
R2:R4	R2	=Q2-K2
F2:F4	F2	=IF(OR(D2="BioMed",D2="BioTech"),(SUMIFS(Table33[Rate],Table33[Grade],C2,Table33[PI],E2)),(SUMIFS(Table57[Rate],Table57[Rank],C2,Table57[PI],E2)))
H2:H4	H2	=IF(D2="BioTech",(SUMIFS(Table29[C3Val],Table29[C3Pts],G2)),(SUMIFS(Table31[C2 Val],Table31[C2 Pts],G2)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A3:A4	Any value
S2:S4	List	Yes,No
G2:G4	Whole number	between 0 and 216
I2:I4	Whole number	between 0 and 168
L2:L501	Any value
M2:M501	Any value
C2:C501	List	=Lists!$A$2:$A$7
D2:D501	List	=Lists!$A$10:$A$12
E2:E501	List	=Lists!$C$2:$C$6

 

[Fluff]

Thanks for that, unfortunately it doesn't really help. I would need to see the data you are trying to lookup, along with an explanation of what should be returned.

 

[Decryptix]

Good morning,

I thought as much. Would a copy of the spreadsheet work best or just a snippet of the Data Tables?

 

[Fluff]

A copy of the worksheet is fine.

 

[Decryptix]

Here's the sheet: New Pay Calculation - Engineers.xlsx

Info:
Currently BioMedical and BioTechnology engineers are paid on the same scale (Table 33) with Chem being paid slightly more (Table 57), with BioTech getting an extra allowance for their field work (Table 28). More time in the field = higher allowance. All three engineer groups receive an allowance (C2/C3 Val/Pt tables on Lists worksheet - Table 31/29 Respectively). Chem and BioMed are C2, BioTech is C3.

We're restructuring their pay to focus less on allowances and turn a significant portion of their earnings that weren't pensionable before into pensionable earnings. So under the new guidance BioTech is paid at their new pay scale with a maximum of 14 pay increments per grade. G1 is our more junior, G6 is our more senior. BioMed and Chem engineers are are paid at their two respective tables at a maximum of 17 increments (Table 3 for all three with BioTech at the Top then Chem/BioMed.)

Due to contractual obligations and the like, unless someone tops out due to the recalculations of pay we have to always ensure to pay individuals more than what they're currently making during these types of restructures. Those individuals who've been with us for a significant amount of time are aware they may lose a bit in their take home, but it's going to effectively double their pension, so they're less concerned about the long term loss.

We're using this spreadsheet to do a quick comparison of their current pay & allowances vs their time in grade. Whichever value results in a higher pay increment, is what they get. We just need to ensure that whatever value is populated in Col N is no higher than their Specialty allows (14 for BioTech, 17 for the others). So the adjustment you provided was great, but when we have different scales based on two groups, no pay increment level 17 exists for BioTech.

I hope that provides enough context, as I'm trying to generate this within the guidelines of policy and have got it to the 99% solution, but when I was trying to break it before pushing it to our Admin staff, I noticed that anyone higher in Pay & Allowances than their specialty and grade allows, would spit out an error. This is why QA is important!

Please let me know if there's something else you need clarified. I'm still trying to bash it out on my end, just haven't had much of a chance to look at it today.

 

[Fluff]

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