+ Reply to Thread
Results 1 to 3 of 3

Formula/s to find a certain number in a group/cell and then do calculations.

  1. #1
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Formula/s to find a certain number in a group/cell and then do calculations.

    Hi folks

    The attached sample sheet gives a target number in A2.

    Col B lists various combinations of numbers (always 4 numbers)

    Cells C1:H1 display the numbers that are LOWER than our targeted number (7)

    Cells C2:H2 show a value for each of the numbers (1 to 6) that are lower than (7)

    What I want to do from Col B is find every Cell with our targeted number (7) and then show the PRODUCT of the number/s (from the value cells for each) lower than our target.

    Our targeted number will never be the first number, but can be 2nd, 3rd or fourth number but will not repeat in the same cell.

    We have no interest in this sample for numbers 8 and above.

    In Row 3 I have just manually input the values from numbers 1,2,3 and shown the product in Cell I3 to show what I want to achieve.

    Hope that makes sense.

    Cheers
    Hammer
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula/s to find a certain number in a group/cell and then do calculations.

    Put this formula in C3, then copy down and across through column H:

    =IF(ISNUMBER(SEARCH(","&C$1&",", ","&$B3&",")), C$2, "")


    Copy your formula I3 downward.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Formula/s to find a certain number in a group/cell and then do calculations.

    Hi JBeaucaire
    That does the trick but are we able to leave all the cells in C:H blank that do not have our targeted number? (I should have mentioned I need to do that, apologies).
    When I continue on with other sheets the targeted number will change so I need the formula to recognise the number I will have in A2
    Cheers
    Hammer

+ 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. Find a number combination in a group of cells
    By OpieWinston in forum Excel General
    Replies: 6
    Last Post: 03-15-2014, 10:45 AM
  2. Replies: 0
    Last Post: 07-07-2012, 04:13 AM
  3. Replies: 2
    Last Post: 02-08-2012, 06:51 AM
  4. [SOLVED] find doubles in number group!
    By stewart08 in forum Excel General
    Replies: 4
    Last Post: 02-19-2009, 09:44 AM
  5. Find the largest number by group and populate
    By sanjimmy in forum Excel General
    Replies: 2
    Last Post: 07-19-2006, 04:19 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