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

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

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

Thank you XOR. Greatly appreciated.

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

You're very welcome!

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

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

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

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.

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

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),"")

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1