Forum Discussion
How Do I Calculate Different Cells Using Different Formulas?
Try this:
=(N5+Q5*(Q5<0))*C5+B6The 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 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+B6This results in 2148.63
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!!!!
Try this:
=(N5+Q5*(Q5<0))*C5+B6The 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.
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
Nickerz_2020 You need to use ROUND in one or more of your formulae to force Excel to calculate with rounded numbers. Merely formatting a number to display two decimals is not enough. Calculations will still be performed on the un-rounded numbers. So, in Q5, you would have to enter:
=ROUND(your formula expression, 2)This will fix the outcome to -4.08. You'll probably need to do this with more of your calculated numbers.
Sorry, my mistake...I thought the formula was a little off but my report was off. When I fixed it your formula above worked again!!!!!!
THANK YOU SIR! Very much appreciated!
