Aggregate Function's Array Argument Gives VOLATILE formula result when using concatenation for Named Range

[DR RENO]

Need experts help on this after racking brain over and over. I have a data sheet, 'DATA', that contains Product and Pricing detail by row. Each row will have a column for Product Name (ie. Product A, Product B, Product C....etc), and a column for Location, (ie.Location 1, Location 2, Location 3....etc.) On another worksheet, I have a table that's a pricing schedule, 'PRICING SCHEDULE', for each product at each location. I've created a Named Range for each Product/Location line on the Pricing Schedule based on the previous product and location name example I gave looks like this: PRODA1, PRODB2, PRODC3. The pricing schedule will have the Price for the Product/Location combo.

The same product can be sold at multiple locations with a different price for each. So it will need a unique name in order to obtain the correct value. I'm using the Aggregate function in order to avoid having a longer formula with multiple IF Statements and And/OR operators. I'm just having trouble with the Aggregate functions array argument.

Aggregate works perfectly if I just directly type in the named range, but unfortunately, I have a dynamic file so I'm needing to reference in each data row the product and location to obtain the correct value from the other sheet. This has me creating a concatenation where I combine LEFT function to get the 1st 4 characters of the Product Name field and RIGHT function for the last character on both the Product Name field and the Location Name field. So for a row where the product is, 'Product B' for location 'Location 4', I get a concatenation of, ProdB4 which I have a named range setup as in the Pricing Schedule. Unfortunately, when I reference the cell that contains the concatenation, it generates a VOLATILE formula result. It also generates a Volatile formula result when entering the concatenation directly into the function's array argument. Here is my formula:

AGGREGATE(15,6,INDIRECT(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1))/(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1)))>$P2),1)


If I just type in PRODB4 where I am referencing my named range, the formula works perfectly, as the formula result give me a value. Again, I know there might be another formula option, but I don't want to get into a long IF statement, so would really need to keep using AGGREGATE. I am open to renaming the Named Ranges if you have a better suggestion, as well as obviously any formula changes you recommend.

Any help you can give with this will be greatly appreciated

 

[DR RENO]

I think the problem is that the product range and location range is capturing all of the associated cells and not getting a 1:1 value that is compared with P2, due to Location not being a number. I PM'd you to provide some more information that may help.

 

[Marcelo Branco]

Your formula is using wrong columns.

Try this
=AGGREGATE(15,6,PaySched[[Year1]:[Year10]]/((PaySched[Position]=B2)*(PaySched[Location]=H2)*(PaySched[[Year1]:[Year10]=]=>$P2)),1)

Using this formula some rows still have errors. This occurs when there are no values in Price Schedules, in the corresponding row, higher values than those in column P.

M.

 

[Marcelo Branco]

For some reason the formula is looking wrong.
=AGGREGATE(15,6,PaySched[[Year1]:[Year10]]/((PaySched[Position]=B2)*(PaySched[Location]=H2)*(PaySched[[Year1]:[Year10]=]=>$P2)),1)

Should be
(PaySched[ [Year1]:[Year10] ]>$P2)

M.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...ange-causing-volatile-aggregate-forumula.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Marcelo Branco]

Dr Reno,

Please reply only to the topic - forum rules do not recommend support by PM.
Did you see my last answer?
Your formula is using the wrong columns - in it, replace A2 with B2 and B2 with H2. It worked perfectly for me.

M.

 

[DR RENO]

My apologies to both you and Fluff for not following forum policy. I'm still relatively new to both sites and was not clear on what constituted cross-posting. I won't go into the why's as regardless I broke a rule and will not do it again. I have been essentially working exclusively with Marcelo as he was the first responder who appeared to really understand my issue, which is no slight to any other poster who responded as it very well could have been a result from my writing. Since Marcelo and I are close to solving this, hopefully, we can continue on.

Marcelo I did see both your posts. I was confused on the columns part because I believe I've mapped those according to how you mapped out, albeit in a different file than our example(s) which have different table and range names. I did remove the = that you pointed out. Good catch by you.

The formula change that you provided I believe I'm using and does appear to be working....sort of. It will not carry out past year-2 and in year-2 there are a couple of rows where transaction prices exceed max price but is not keeping transaction price. That seems like that is something outside the Aggregate formula though. These are the cell results I'm receiving after updating formulas following your post

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.dropbox.com/s/e8egh8g3sddtv44/PROD%20DATA_EXCELHELP_10Jun2019.xlsx?dl=0[/FONT]

 

[Marcelo Branco]

I don't understand what you are trying to do.
You changed the formula i suggested - it don't use named ranges.
Keep the formula as it is, without named ranges - they are not necessary and complicate things.

M.

 

[DR RENO]

Unfortunately the ranges you gave in your solution proposal,

=AGGREGATE(15,6,'PRICING SCHEDULE'!C$2:F$100/(('PRICING SCHEDULE'!$A$2:$A$100=A2)*('PRICING SCHEDULE'!B$2:B$100=B2)*('PRICING SCHEDULE'!C$2:F$100>$P2)),1), are Named Ranges. They are being utilized in other areas of my workbook including VBA code and are peforming as expected. I'm not aware of a way I can get around that. I don't see that as the problem though, as when I go and modify range names that have lost the field name (ie. Position or Location), they update correctly. I will just need to do that for each cell in first row.

The only problem I'm still having is the Number Errors when the max price has been reached. I'm not seeing anything in the formula outside of the Aggregate function that would generate that, so I'm not sure if it's a sticking part from solution or not.
​

 

[Marcelo Branco]

Thoughts

1. Your formula
=IF(AND($D2>=P2,$B2="POSITION A",$B2="POSITION W"),P2,IF...)

The AND in red makes no sense: B2 cannot be equal to POSITION A and to POSITION W at the same time

2. Avoid structured references because they do not work well when the formula is dragged to the right.
Try something like this
AGGREGATE(15,6,'Price Schedules'!$C$2:$K$28/(('Price Schedules'!$A$2:$A$28=$B2)*('Price Schedules'!$L$2:$L$28=$H2)*('Price Schedules'!$C$2:$K$28>P2)),1)

M.

 

[DR RENO]

On Point-1, I moved it to be an AND/OR as I think you have a valid point. However, it's not the cause for the #NUM error. When I exceed max price in the array, the formula errors out. Here is an earlier formula that I created for essentially the same requirement.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]IF(C12>=$L$2,C12,AGGREGATE(15,6,PositionC/(PositionC>C12),1))[/FONT]

The only difference is that was Position only, so Location was not factored in. That formula would just repeat max following years. I'm not seeing why this formula won't, but I'm getting errors when it reaches that point. Prior activities are performing as expected. Thoughts?