Excel Problem
Excel Problem
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Excel Problem

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    San Antonio, TX
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Found your web while browsing for help with an Excel formula, or combination that I was trying to make work. Maybe you can help me, maybe you canít. Letís say I need cell C38 to pick from range C10:C30 all values of ď5Ē. When it finds all these ď5Ē values, I need it to pick the highest value in cell range D10:D30 that are in same row as the C column ď5Ē value, even if more than one are equal values, and put this value in cell C38. Iíve been trying to combine LARGE, SUMIF,COUNTIF formulas, but cannot get the right syntax or combination. Is this some kind of nested function? I am not too advanced on Excel, but am learning. I appreciate any help you can provide.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,846
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-10 12:15, AMS wrote:
    Found your web while browsing for help with an Excel formula, or combination that I was trying to make work. Maybe you can help me, maybe you canít. Letís say I need cell C38 to pick from range C10:C30 all values of ď5Ē. When it finds all these ď5Ē values, I need it to pick the highest value in cell range D10:D30 that are in same row as the C column ď5Ē value, even if more than one are equal values, and put this value in cell C38. Iíve been trying to combine LARGE, SUMIF,COUNTIF formulas, but cannot get the right syntax or combination. Is this some kind of nested function? I am not too advanced on Excel, but am learning. I appreciate any help you can provide.
    In C38 enter:

    =SUMPRODUCT(MAX((C10:C30=5)*(D10:D30)))

    Is this what you're looking for?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello AMS

    You can secure the amount with an Array Formula. You MUST enter an array formula with Ctrl-Shift-Enter (CSE) instead of just Enter and you must also use CSE if you edit the formula.

    =MAX(IF(C10:C30=5,(D10:D30)))

    or use equivalent which does not require array entry

    =SUMPRODUCT(MAX((C10:C30=5)*(D10:D30)))



    [ This Message was edited by: Dave Patton on 2002-04-10 12:32 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    San Antonio, TX
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The SUMPRODUCT command worked great. Obrigado. Now, if I wanted to keep the original formula and add J10:J30 to find the "5" value and I10:I30 for the largest value next to the J cell with a "5" and pick the largest value from Col D & I next to the cell with a "5", how would that be written?

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,846
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-10 12:50, AMS wrote:
    The SUMPRODUCT command worked great. Obrigado. Now, if I wanted to keep the original formula and add J10:J30 to find the "5" value and I10:I30 for the largest value next to the J cell with a "5" and pick the largest value from Col D & I next to the cell with a "5", how would that be written?
    Not sure I understood the request, but it looks like:

    =MAX(SUMPRODUCT(MAX((C10:C30=5)*(D10:D30))),SUMPRODUCT(MAX((J10:J30=5)*(I10:I30))))

    Right?






  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    San Antonio, TX
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ††
    Aladin--that did it. Thanks a bunch.

    AMS

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com