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 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.
=SUM(O5:P5)
...so I typed the following in cell Q5...
=ROUND(O5:P5, 2)
...but I get #VALUE! error in both cells Q5 and now T10 (which is the total). ???
Nickerz_2020 Use =ROUND(SUM(O5:P5),2)
Hi again Riny,
Can you please help with this new issue? Is there a way around it as I have to do this every time I open my spreadsheet now?
When I now open my excel spreadsheet I'm prompted to edit it (see #1 attachment below) and then when I save it and close it and open it again, I'm prompted to Update it, not update it or click on Help (see #2 attachment below). If I click on the Help button, it opens a help link to the following...
Help Link:
https://support.microsoft.com/en-us/office/control-when-external-references-links-are-updated-21e995b5-bab1-4328-8ab3-dd357fe0e653?ns=excel&version=16&syslcid=1033&uilcid=1033&appver=zxl160&helpid=xlmain11.chm196754&ui=en-us&rs=en-us&ad=us
Nickerz_2020 Better to open a new thread if the question is not related to the original question.
As to the warning messages, I believe it's clearly explained in the article. It's basically a safety measure so that you do not accidentally connect to and update links to an external data source. So, you need to judge yourself if you want to update the links or not. It's just one click.
- YEP! That worked!!! THANK YOU AGAIN! You are awesome Riny!!!! 😃
