# Modify array formula returning value associated with minimum price calculation

1. ## Modify array formula returning value associated with minimum price calculation

I have an array formula, which I do not understand enough to modify in column J of worksheet “Item_data”.

=IF(VLOOKUP(ItemTable[[#This Row],[PPT]],PriceTypeTable,3,FALSE)>0,LOOKUP(1,1/FREQUENCY(-9^9, IF((Basis="C")*(PriceType=ItemTable[[#This Row],[PPT]]),Multiplier,9^9)), CustPrcType),"")

The formula returns the first value of the Customer Type associated with the minimum customer price from Column G in worksheet “Type4_data”.

The requirement is now to return the first value of the Customer Type associated with the maximum customer price from Column G in worksheet “Type4_data”.

Could someone provide me with the correct syntax and explain?  Register To Reply

2. ## Re: Modify array formula returning value associated with minimum price calculation

Hi.

Actually there's no need for an array formula if you structure the syntax appropriately.

And if the only change is to return the first value associated with the maximum as opposed to the minimum, then, in J2:

=IF(VLOOKUP([@PPT],PriceTypeTable,3,FALSE)>0,LOOKUP(1,1/FREQUENCY(0, 1/((Basis="C")*(PriceType=[@PPT])*Multiplier)),CustPrcType),"")

Regards  Register To Reply

3. ## Re: Modify array formula returning value associated with minimum price calculation

Thank you XOR. Greatly appreciated.  Register To Reply

4. ## Re: Modify array formula returning value associated with minimum price calculation

You're very welcome!  Register To Reply

5. ## Re: Modify array formula returning value associated with minimum price calculation

The 81.4 KB file shows the N/A error in column J of worksheet "Item_data".  Register To Reply

6. ## Re: Modify array formula returning value associated with minimum price calculation

Not only is there not a single error in column J of that sheet, there is not a single error in the entire sheet!

Regards  Register To Reply

7. ## Re: Modify array formula returning value associated with minimum price calculation

I don't know how to remove an attachment, so there are two files with the same name. The file being displayed with the 81.4KB size has the error in Column J in worksheet "Item_data".

Thx,
jdfjab  Register To Reply

8. ## Re: Modify array formula returning value associated with minimum price calculation

ErrorPic.jpg
This is what I'm showing, in column J. (attaching snapshot as jpeg-I think )
Sorry, guess I don't know how to do that, either.  Register To Reply

9. ## Re: Modify array formula returning value associated with minimum price calculation

I think you need to re-check your attachments.

I've just re-downloaded the 81.4KB version, navigated to the tab Item_data, and, like I said, there is not a single error in the whole of that sheet.

Regards  Register To Reply

10. ## Re: Modify array formula returning value associated with minimum price calculation

When I open the file directly. column J, worksheet "Item_data" is filled with "N/A". Can it be a setting in Excel itself that is causing the error when I open it, but not when you do? (I also downloaded the file using "save as" with the same resulting "N/A" error in all of column J (less the header row), worksheet "Item_data".

I'm baffled.

I've removed the original file, leaving only the file which when I open has the N/A error in column J on worksheet "Item_data".

Additionally I believe it is being caused when the 1/((Basis="C") part of the formula generates #DIV/0!

=IF(VLOOKUP(ItemTable[[#This Row],[PPT]],PriceTypeTable,3,FALSE)>0,LOOKUP(1,1/FREQUENCY(0, 1/(({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*(PriceType=ItemTable[[#This Row],[PPT]])*Multiplier)),CustPrcType),"")  Register To Reply