+ Reply to Thread
Results 1 to 3 of 3

Help on if statements calculations or best method

  1. #1
    Forum Contributor
    Join Date
    10-09-2010
    Location
    London
    MS-Off Ver
    Excel Version Office 365
    Posts
    211

    Help on if statements calculations or best method

    Could someone please guide me as to what the best was is to achieve my formula - IF or Sumproduct.

    I have atatched the spreadsheet and the desired result in Cell Q16.

    The answer should be 73,906. Cell Q15 should be zero and for example Cell Q25 should be 33,529 x 0.9

    Any help would be greatly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help on if statements calculations or best method

    =IF(I16="yes",INDEX($T$3:$Z$3,MATCH(C16,$T$2:$Z$2,0))*F16,"")
    so put in q4 and drag down
    =IF(I4="yes",INDEX($T$3:$Z$3,MATCH(C4,$T$2:$Z$2,0))*F4,"")

    not everything is in the range t3:z3 like el or dl you'll have to add them and extend the range from t3:z3 to t3:ab3 or bigger
    Last edited by martindwilson; 07-16-2012 at 09:07 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Help on if statements calculations or best method

    Put this formula in cell R4:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then copy it across into S4:Z4. Then copy R4:Z4 down as far as you need to.

    Hope this helps.

    Pete

    EDIT: Ah, slightly misunderstood your requirements, but if you put this in Q4:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    along with the other formulae, then you will have your results in column Q.

    Pete
    Last edited by Pete_UK; 07-16-2012 at 09:11 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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