How Do I Calculate Different Cells Using Different Formulas?

[Nickerz_2020]

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.

[#2]

@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 

[#3]

@Nickerz_2020 

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)

[#4]

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!!!!

[#5 · Accepted solution]

@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.

[#6]

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!

[#7]

@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!

Format Cell Screenshot 

[#8]

@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.

[#9]

The formula in Q5 is...

=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). ???

[#10]

@Nickerz_2020 Use =ROUND(SUM(O5:P5),2)

[#11]

YEP! That worked!!! THANK YOU AGAIN! You are awesome Riny!!!! =)

[#12]

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-21e995...

[#13]

@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.

[#14]

Ok I'll open a new thread then if this is unrelated but but I was thinking this is related to the last formula since it wasn't happening before that. ? ?

[#15]

@Nickerz_2020 The comment regarding a new thread was a tip for the future. Anyway, in the Trust Center (File, Options) you can change the settings. You'll see that "Enable automatic update of links is NOT RECOMMENDED.

[#16]

Got it, thanks for the tip Riny and the quick reply!

I guess I'm confused as to why this suddenly started happening. ? I don't share this spreadsheet and I've only been updating the formulas from these forums, which is why I thought this has something to do with the recent formulas I copied and pasted.

I opened my Trust Center and I have all the default options that you show in your screenshot above but to try to get rid of the pop up window (which now happens each time I open my spreadsheet, no matter which option I choose, update/don't update) I tried selecting "Disable..." for the 3 options above but I'm still being prompted to update/don't update for the latest data. ????

Again, I'm just confused as to what my spreadsheet is trying to do but even more confused as to why I'm now having to deal with this when I haven't changed any of these settings. Just weird...

***UPDATE***
Just now, I clicked on the Data tab>Existing Connections but found none (see attached screenshot below...)

So then I tried Data>Edit Links>Break Link

...and it pops up a warning, I then clicked on Break Links (since I have another copy of this spreadsheet saved) and I'm no longer prompted to Update the links!

My spreadsheet looks fine as do the formulas (as far as I can tell at the moment) but again, I have NO idea how this issue started. ? Just came out of left field all of a sudden. ?????

[#17]

@Nickerz_2020 A link to an external workbook must have been inserted somewhere at some point. You can use the tool in the attached link to find it and decide what to do with it.

https://manville.org.uk/software/findlink.htm 

[#18]

@Riny_van_Eekelen 

Yep, that tool found it and I was able to correct it! Thanks again Riny!!!

Turns out that my spreadsheet had a tab (Billing Wk #06 - this was the invoice week I found that was positive so the formula above needed to be revised to round up) and when I was copying and pasting formulas, I accidentally pasted the wrong info into a different cell with a formula (note to self...see your screenshots for today for details in case you have to reference this fix in the future). So I feel much better now knowing exactly why this started happening and I was able to correct it thanks to that very useful and easy to use findlink tool!

I had to add the findlink as an add-in even though it says it can be ran once. I did this using the steps they had in that link... 

• To install as an add-in in Excel 2010 or later
  • File > Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse to where you saved FindLink.xlam, Select FindLink.xlam and click OK

Below are some screenshots with more detail...

findlink Add-in Tool (added to the Ribbon in 'Data' Tab):

Found Formula Link Screenshot:

Confirmed - No Excel Formula Links (after correcting the formulas myself instead of having findlinks delete them - I just wanted to be sure and glad I did it myself as the cells still had formulas so not sure if those would have been lost):

No Excel Links Found:

You're the best Riny and you have been VERY helpful this entire time. Again, thank you sir!

Nick