Forum Discussion

animaltraceminerals's avatar
animaltraceminerals
Copper Contributor
Apr 27, 2020

I need help writing a formula please

    I need A5 to subtract B5 and C5  (and D5 only if value in E5 is "0") and provide the difference in F5   All in the same formula.   I intend to copy the formula down the column.   Please. ...
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 28, 2020

    animaltraceminerals Can you show the error? Upload the workbook or a screenshot that shows the formula and the result.

    • animaltraceminerals's avatar
      animaltraceminerals
      Copper Contributor
      Apr 28, 2020

      Riny_van_Eekelen 

       

      =A5-B5-C5-D5*(E5=0)
      gives me a return of "#VALUE!"

       

      But I found another thread with a similar question. This was one of the responses given.

      =IF(A2>0,B2-C2,A2)

      With this explanation:

      "The above formula says:

      IF cell A2 greater than 0, then subtract cell C2 from B2 and return the result.

      Otherwise, return the value of cell A2 as it is."

       

      So I tweaked it to work for what I needed and it worked. 

      =IF(E5=0,A5-B5-C5-D5,A5-B5-C5)

      Using the previous explanation format, 

      If Cell E5 equals 0, then subtract cells B5, C5 and D5 from A5 and return the result. Otherwise, subtract only cells B5 and C5 from A5.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 28, 2020

        animaltraceminerals 

        It's interesting how did you receive #VALUE! in first formula, perhaps you may share sample file?

        By the way, you may simplify second formula

        =A5-B5-C5-IF(E5=0,D5,0)

        All mentioned in the thread formulas are equivalent.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Apr 28, 2020
        • SergeiBaklan Perhaps I indeed missed the A5 at the beginning. Wasn't entirely sure how to interpret the question. I read is so that the result would be in A5.

Resources