+ Reply to Thread
Results 1 to 3 of 3

Product formula does not work correctly. How to make it work?

  1. #1
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    365 (work-99%), 2016 (home-1%)
    Posts
    266

    Product formula does not work correctly. How to make it work?

    Hi forum,

    I asked a question previously on how I could multiply two matrices together if given a certain set of letters from A-E and multiply the correspondents together with this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    E.g. the absolute value to product $H$1:$L$1 (A-E) and the row that it goes down corresponding to the formula cell row is $H2:$L2. It then matches.
    E.g. for AxE find columns A and E, find the row that matches the cell the formula is in, multiply the column value of A and E. Achieve result.
    This works for a matrix from A-E, but it does NOT work for a matrix from S - G - F... and I'm not sure why.
    I have attached my sheet for demonstration.
    the FxG column is completely wrong.
    FxS column is correct

    EDIT: I tried changing the letters back to A,B,C and it works. I think it has something to do with letter order for some reason.
    Last edited by JulianS96; 10-06-2020 at 06:07 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Product formula does not work correctly. How to make it work?

    Lookup use Aproximate Match , L1:N1 must be sorted ascending F - G - S

    If L1:N1 not sorted then try at
    O2
    =PRODUCT(IFNA(HLOOKUP(T(IF(1,MID(O$1,{1,3,5},1))),$L$1:N2,ROWS(O$1:O2),0),1))

    or
    =PRODUCT(IFNA(INDEX($L2:$N2,N(IF(1,MATCH(MID(O$1,{1,3,5},1),$L$1:$N$1,)))),1))

    Array enter

  3. #3
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    365 (work-99%), 2016 (home-1%)
    Posts
    266

    Re: Product formula does not work correctly. How to make it work?

    Ah so it is to do with letter order. Thanks Bo_Ry, knew there'd be a workaround!

+ 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. [SOLVED] Vlookup formula does not work correctly
    By Reykjavik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-13-2018, 12:07 PM
  2. How to make countifs work correctly
    By Delta729 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2016, 06:55 PM
  3. [SOLVED] Formula doesn't work correctly
    By Chris McGlothen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2016, 07:28 PM
  4. [SOLVED] How to make the macro work for all rows in the work sheet
    By Valli nayaki in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2013, 10:43 PM
  5. Need help on Logic to make formula work correctly
    By cdotyii in forum Excel General
    Replies: 1
    Last Post: 06-06-2012, 08:23 AM
  6. Help Please. Trying to get this formula to work out correctly.
    By bryanr72 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-29-2008, 10:43 PM
  7. Replies: 1
    Last Post: 04-07-2005, 04:06 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