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
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