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.
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.
Excel Formula:
=AGGREGATE(15,6,Lists!X2:X307/((Lists!V2:V307=C2)*(Lists!W2:W307=D2)*(Lists!Y2:Y307>K2)),1)