Forum Discussion

Nickerz_2020's avatar
Nickerz_2020
Brass Contributor
May 13, 2021
Solved

How Do I Calculate Different Cells Using Different Formulas?

I have multiple cells that require different calculations within the formula (I should also point out, that one the cells (Q5) is SOMETIMES a negative number and other times it's positive). Below is ...
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 14, 2021

    Nickerz_2020 

    Try this:

    =(N5+Q5*(Q5<0))*C5+B6

     The part (Q5<0) return TRUE (or 1) is Q5 is negative, FALSE (or 0) if positive. Hence, in the first instance you will multiply Q5 by 1, in the other instance Q5 by zero.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 13, 2021

Nickerz_2020 You haven't really explained the calculation logic to arrive at the target value of 2148.74

When I play around with the values given, I can get pretty close with the following formula:

=(N5+Q5)*C5+B6

This results in 2148.63 

 

  • Nickerz_2020's avatar
    Nickerz_2020
    Brass Contributor
    May 13, 2021
    Yes, this absolutely worked and thank you very much!

    I tested it on several other weeks and they all worked and matched the invoice 100%

    Now, one more thing and this my get complicated (hopefully not)...

    I tested this on a week when cell Q5 was NOT a negative number (4.44 hours instead of -4.44 hours). Is there a way for excel to know if cell Q5 is a negative number, it applies the formula you gave above BUT if cell Q5 is a positive number, it doesn't add Q5 or ignores the positive number? Not sure if I'm explaining this right but the only time Q5 should be added to N5 is when Q5 is a negative number.

    Thanks again!!!!
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      May 14, 2021

      Nickerz_2020 

      Try this:

      =(N5+Q5*(Q5<0))*C5+B6

       The part (Q5<0) return TRUE (or 1) is Q5 is negative, FALSE (or 0) if positive. Hence, in the first instance you will multiply Q5 by 1, in the other instance Q5 by zero.

      • Nickerz_2020's avatar
        Nickerz_2020
        Brass Contributor
        May 15, 2021

        Riny_van_Eekelen 

         

        I found an issue with how one of my cells is being calculated. I think it has to do with how many decimals the formula is using so I checked and it's set to 2 decimals (see attached screenshot & I linked it below...) but my total is slightly off compared to the invoice. In this case, it's only off by $0.03 cents but it does have to be exact.  So using the following formula...

         

        =(N5+Q5*(Q5<0))*C5+B6

         

        it works for some weeks but it's not working for Billing Wk #17 as my formula totals $2,486.67 but the invoice totals $2,486.70.

         

        Now...if I manually type "4.08" directly into cell Q5, then my total matches the invoice exactly ($2,486.70) but since the total in Q5 is being calculated from additional numbers beyond the decimal point (i.e., -4.083333333333333) it's throwing off my total slightly.

         

        Is there a way around this?

         

        Thanks!

         

        https://lh3.googleusercontent.com/OQd9MiKLXXVpOF2iTy4_Lko7jU7UYat6zPf5FapEZrxo-Kvm7CuGv-VGl3kfMN3Ur0IfmuICOxKYMUFxxZ9buGaMWSGTfAZkKyZo-vHV8IlWcTjRbDmiB-C93u0saO3ETbGNg_e9evw=w2400 

Resources