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

 

[Marcelo Branco]

Let me see if I understand your problem.

Data on PRICING SCHEDULE

	A​	B​	C​
1​	Product​	Location​	Price​
2​	Product A​	Location 1​	100​
3​	Product B​	Location 1​	150​
4​	Product C​	Location 1​	200​
5​	Product A​	Location 2​	95​
6​	Product B​	Location 2​	160​
7​	Product C​	Location 2​	205​
8​	Product A​	Location 3​	110​
9​	Product B​	Location 3​	148​
10​	Product C​	Location 3​	190​

<tbody>
</tbody>

DATA sheet

	A​	B​	C​
1​	Product​	Location​	Result​
2​	Product A​	Location 1​
3​	Product A​	Location 2​
4​	Product A​	Location 3​
5​	Product B​	Location 1​
6​	Product B​	Location 2​
7​	Product B​	Location 3​
8​	Product C​	Location 1​
9​	Product C​	Location 2​
10​	Product C​	Location 3​

<tbody>
</tbody>

You need a formula in column C to get the corresponding prices in PRICING SCHEDULE. Am i right?

M.

 

[Marcelo Branco]

Question

Why are you using >$P2 in your formula?

M.

 

[DR RENO]

You correctly identified what I'm needing Marcelo. I'm hoping to be able to utilize this Aggregate function for this solution, as it will be nested inside another multiple IF Statement formula which is already using 3 lines. I'm trying to avoid a super long formula if possible. Thank you for taking a look at this. Based on your response showing how you quickly picked up what my rather wordy and confusing post was trying to convey, I'm feeling confident you will know how to make the function work as needed for my multiple criteria data row(s).

 

[Marcelo Branco]

You correctly identified what I'm needing Marcelo. I'm hoping to be able to utilize this Aggregate function for this solution, as it will be nested inside another multiple IF Statement formula which is already using 3 lines. I'm trying to avoid a super long formula if possible. Thank you for taking a look at this. Based on your response showing how you quickly picked up what my rather wordy and confusing post was trying to convey, I'm feeling confident you will know how to make the function work as needed for my multiple criteria data row(s).

Have you seen my question about >$P2?

M.

 

[DR RENO]

Question

Why are you using >$P2 in your formula?

M.

My Pricing Schedule goes out incrementally. So for your example, Column D would have 105, Column E 115, Column F 125, etc.... The >$P2 is referencing Year 1's Price, which is essentially the transaction price against how many prices in named range it's greater than or equal to. Additional year price changes will be based of where that price falls on the Pricing Schedule. The catch that comes with this, and why the Aggregate function with the kth is needed, is that the salesperson might transact a price outside of the one found in Col C of pricing schedule. So this formula will allow the correct identification to be made for the correct incremental pricing for Year 2. Example, Data sheet has a transaction price of 107, so the following year (Year 2) the formula result would show 115. Good question and I hope that helps to clarify. Let me know if not. Thanks, M!

 

[Marcelo Branco]

I am confused about the data setup in the SCHEDULE PRICING worksheet - are there values in columns D, E, F etc?

Could you show a small sample of these data (about 5 rows)?

I was thinking that prices were just in column C as I showed in post 2.

M.

 

[DR RENO]

	A​	B​	C​	D	E	F
1​	Product​	Location​	Year1​	Year2	Year3	Year4
2​	Product A​	Location 1​	100	110	115	120
3​	Product A​	Location 2​	150	175	200	225
4​	Product A​	Location 3​	200	210	215	220
5​	Product B​	Location 1​	95	105	120	135
6​	Product B​	Location 2​	160	200	240	280
7​	Product B​	Location 3​	205	210	215	220
8​	Product C​	Location 1​	110	115	120	125
9​	Product C​	Location 2​	148	150	160	170
10​	Product C​	Location 3​

<tbody>
</tbody>

One other item to note, although it really doesn't impact the AGGREGATE function in any way, is that the transaction price can also be less than Year1. This is what caused another If Statement, as well as there are some products that are fixed price vs variable pricing. The fixed/variable pricing is only for certain products (ie. Product G, Product L), so I used another if statement to denote those. It another reason I really want to stick with Aggregate, as it does exactly what I'm needing. If I directly type the named range, it gives me the expected value from Pricing Schedule. However trying to reference cells on DATA sheet to derive the named range has been a struggle

 

[Marcelo Branco]

See if this formula in C2 of Data sheet does what you need

C2 copied down
=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)

M.

 

[DR RENO]

It's strange because your formula does handle the array argument correctly (which is great by the way and what I needed), but I'm receiving a Number Error in the C2. Why that is strange is because the Option argument in the Aggregate function is 6, which ignores error values, which are to be expected as depending on the Pricing Schedule range it's looking at will have #Div/0,#Div/0...until dividing by 1. So I think it's really close, I just need to figure out why I'm getting that error value. If I type in the named range that I set up, it does the same inside array's formula argument where it shows,

#Div/0,#Div/0, but then it gives an actual value in formula result instead of an error.​