Adding to or adjusting a formula
Results 1 to 6 of 6

Thread: Adding to or adjusting a formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Location
    Lincolnshire, England
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding to or adjusting a formula

    Hi All,
    Need some help please, I have the following formula thatworks as it should so no problem there however I’m trying to amend it or add a secondformula to the front of it so that the formula returns a value of zero based onspecific text in the cell C2 (house).
    The working formula as it is will return a value based onthe calculation of cell V32 which can range from 500 to 100,000
    =IF(($V32+$W32+SUMPRODUCT($Z$21:$AF$21,$Z32:$AF32))< data!$o$13,0,(($v32+$w32+sumproduct($z$21:$af$21,$z32:$af32))-data!$o$13)*data!$o$16)
    Any help would be fab.

    Steve
    Last edited by Fluff; Aug 12th, 2019 at 08:21 AM. Reason: Corrected formula

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Adding to or adjusting a formula

    Is that your actual formula?
    Looks odd to me.

    Does it contain < > characters?
    The forum often displays posts with < > characters incorrectly.
    To get round this places spaces around the < > characters.

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Lincolnshire, England
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding to or adjusting a formula

    formula is correct and calculates correctly. I want to add something like =IF(AND(C32="House"),"0")

    when I add this to the formula I don't get the correct value out
    =IF(AND(C32="house"),"0",0)&IF(($V32+$W32+SUMPRODUCT($Z$21:$AF$21,$Z32:$AF32))< data!$o$13,0,(($v32+$w32+sumproduct($z$21:$af$21,$z32:$af32))-data!$o$13)*data!$o$16)

    When I add the word house to cell c32 it should return a 0any other value should return a the exact figure that I have prior to addingthe additional formula but nothing works for me.
    Last edited by Fluff; Aug 12th, 2019 at 08:49 AM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Adding to or adjusting a formula

    Try
    =IF(C32="house",0,IF(($V32+$W32+SUMPRODUCT($Z$21:$AF$21,$Z32:$AF32))< data!$o$13,0,(($v32+$w32+sumproduct($z$21:$af$21,$z32:$af32))-data!$o$13)*data!$o$16))

    Also please note what Special-K99 said about putting spaces around the < & > signs in formulae.
    I have corrected both your posts.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Location
    Lincolnshire, England
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding to or adjusting a formula

    that works a treat, thank you very much..

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Adding to or adjusting a formula

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •