+ Reply to Thread
Results 1 to 10 of 10

Modify array formula returning value associated with minimum price calculation

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    Rockland, WI
    MS-Off Ver
    Excel 2016
    Posts
    56

    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?
    Attached Files Attached Files
    Last edited by jdfjab; 11-25-2014 at 06:04 PM. Reason: Solution error

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    01-12-2012
    Location
    Rockland, WI
    MS-Off Ver
    Excel 2016
    Posts
    56

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

    Thank you XOR. Greatly appreciated.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

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

    You're very welcome!

  5. #5
    Registered User
    Join Date
    01-12-2012
    Location
    Rockland, WI
    MS-Off Ver
    Excel 2016
    Posts
    56

    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. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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. #7
    Registered User
    Join Date
    01-12-2012
    Location
    Rockland, WI
    MS-Off Ver
    Excel 2016
    Posts
    56

    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. #8
    Registered User
    Join Date
    01-12-2012
    Location
    Rockland, WI
    MS-Off Ver
    Excel 2016
    Posts
    56

    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.
    Last edited by jdfjab; 11-25-2014 at 06:35 PM.

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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. #10
    Registered User
    Join Date
    01-12-2012
    Location
    Rockland, WI
    MS-Off Ver
    Excel 2016
    Posts
    56

    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),"")
    Last edited by jdfjab; 11-25-2014 at 06:57 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-24-2013, 12:13 PM
  2. [SOLVED] How would I modify calculation to have value = zero (0) after reaching a minimum.
    By Jim15 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-18-2013, 02:14 PM
  3. Replies: 4
    Last Post: 08-15-2012, 09:49 AM
  4. Replies: 3
    Last Post: 05-25-2012, 03:16 AM
  5. formula for unit price with minimum amount
    By yvanblanchette in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2008, 11:55 PM

Bookmarks

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