Forum Discussion
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 the data currently in each cell...
Q5 is -39.03
N5 is 222.36
B6 is $132.00
C5 is $11.00
This is supposed to total $2,148.74.
I thought this was easy but I can't get the correct total. Below is the formula I tried using but I'm getting a total of $1,190.61...
=SUM(Q5-N5+B6*C5)
Please help! Thank you.
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.
17 Replies
With everyone's permission, here is a comparison of the formulas.
So press Mr. Nick G, Riny van Eekelen's formula, with Like, and mark it as the correct answer :)).
...it will be beneficial to more Community members reading here.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
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
- 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!!!!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.
