Sum and subtraction

Occasional Contributor

Sum and subtraction

Hi,

First, I'm sorry for my english (I used the google translator to help me)

i have three cells. a1 a2 and a3.

a3 is sum of a1+a2

a3= a1 +a2, example 8 = 2+6,

but I want also be possible when i put:

a1 = a2-a3 or

a2 = a1-a3

regardless where I put the values it calculates the third value

Thanks

18 Replies

Re: Sum and subtraction

I am afraid that this doesn't make sense.

In the first place, a1 = a2-a3 and a2 = a1-a3 are not equivalent to a3 = a1+a2.

In the second place, what do you want to happen when you change one cell? Which cell should be kept the same, and which one should be changed?

For example, if I change cell A1, should I change the sum in A3, or should I keep the sum the same and change cell A2 so that A3 still equals A1+A2?

And if I change cell A3, should I keep A1 the same and change A2, or should I keep A2 the same and change A1?

Re: Sum and subtraction

While formulas would lead to circular references, it could be done using VBA - if it were clear what the OP actually wants.

Re: Sum and subtraction

Can you explain what it is you trying to accomplish through this calculation?

At the moment, you are experiencing a circular reference in excel since all the cells are referencing one another and no clear "source"/number is manually inputted for a calculation.

Re: Sum and subtraction

So, let me try explain again...

columns:

N. Dev - is to identify the document (In this document (invoices and others) I have products with 6% tax, 23% tax and the total sum of the two tax.
I have to separate the products by tax)

6% - is tax produt (it's not important to what I want right now)

23% - is tax produt  (it's not important to what I want right now)

Total = 6% + 23%

---------------//--------------

I have to enter the values of 6% and 23%, then the sum appears in column "F"

Image i put :   C17+E17 =F17; 5+6=11€;

but sometimes it would be easier for me if I put the value 6% and the total and  "excel" calculate the value 23%,; example F17 - C17 = E17; 11€ - 5€= 6€

or put the value of 23% and the total and calculate the value of 6% : example F17 - E17 = C17; 11€ - 6€ = 5€

regardless  where i places the values, he can calculate the the third value.

Thanks!

Re: Sum and subtraction

This remains problematic.

Let's say you have entered 5 in C16 and 6 in E16. The sum 11 is calculated in F16.

Now you discover that C16 should actually have been 4.

How should Excel decide whether you want to update the total in F16 to 4+6 =10, or you want to adjust E16 to 11-4=7, keeping the total the same?

Do you have a decision rule for that?

Re: Sum and subtraction

Thank you for clarifying. There are multiple ways to "automate" this issue to speed up your process. Are the documents you're using to count the number of products in a spreadsheet or are you manually counting?

If this is already in a spreadsheet, then a function can be used rather than a complex nested formula to allow for dynamic cell inputs.

Re: Sum and subtraction

"Are the documents you're using to count the number of products in a spreadsheet or are you manually counting?"

I dont need count the number of products. I just need to put the total of 6% products, and 23% products then, sum total of 6% and 23%. (i just need the value "€€€", i dont care if i have 1 or 23 products, for example).

example: Invoice nº 2535 - have total tax 6% of 300€ and total 23% tax of 500€; Sum tax = 800€

Thanks

Re: Sum and subtraction

Let me rephrase - is the source you're using for the input already in a spreadsheet?

Re: Sum and subtraction

Great question!

Do you have a decision rule for that?

what can I say? in my documents I always have the sum (F16) of the 6% (C16) and 23% (E16) tax. So, whenever I enter the total value, I want that value to remain, because I'm sure that value (F16) is right.

(I want the total (F16) dont change if I am to introduce it)

Thanks

No. Is a paper!

Re: Sum and subtraction

Got it - looks like the nested IF function will be the best route.

Part 1: Formulas

Your formula(s) would be broken into three parts since you have three columns, which you can see in the example below (by each column):

*you need to adjust the formula for the first case on your end

Column 1  ' Subtracting total from other integer

``=IF(AND(F1<>"",G1<>""),G1-F1,"")``

Column 2  ' Subtracting total from other integer

``=IF(AND(G1<>"",E1<>""),G1-E1,"")``

Column 3  ' Adding both integers

``=IF(AND(E1<>"",F1<>""),E1+F1,"")``

Part 2: Enable iterative calculation

To not get constant circular reference popups, you have to enable type of calculation in the Excel Settings.

Go to File > Options > Formulas > Enable iterative calculation

Re: Sum and subtraction

I lose the formula when I enter a value in the cell,
for example: if I put a value in cell E1 I lose the formula placed there. The same for the other cells

I have to protect the formula so it’s not erased when I put values there

(I used excel 2013)

Re: Sum and subtraction

The point is to lose the formula. It is not possible to retain the formula and input your figure at the same time.

You drag down the formulas (before replacing with actual data) down to the last row you will need it for and input 2/3 numbers.

The remaining formula will calculate based on what is available, making the calculation dynamic.

Re: Sum and subtraction

I gess i need to use VBA

Re: Sum and subtraction

I don't think VBA is necessary - I'm not sure how it would work, that's better to ask for someone is more familiar.

Re: Sum and subtraction

I don't think this is going to work, but you could try the following:

• Right-click the sheet tab.
• Select 'View Code' from the context menu.
• Copy the code shown below into the worksheet module.
• Switch back to Excel.
• Save the workbook as a macro-enabled workbook (.xlsm).
• Make sure that you allow macros when you open the workbook.
• You'll find that there are situations in which the code can't decide what to do, so the sum will not be correct.
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Row < 17 Then Exit Sub
Application.EnableEvents = False
On Error GoTo ExitHere
Select Case Target.Column
Case 3
If Target.Value = "" Then
Target.Offset(0, 3).Value = Val(Target.Offset(0, 2).Value)
ElseIf Target.Offset(0, 2).Value = "" Then
If Target.Offset(0, 3).Value <> "" Then
Target.Offset(0, 2).Value = Target.Offset(0, 3).Value - Target.Value
End If
Else
Target.Offset(0, 3).Value = Target.Value + Target.Offset(0, 2).Value
End If
Case 5
If Target.Value = "" Then
Target.Offset(0, 1).Value = Val(Target.Offset(0, -2).Value)
ElseIf Target.Offset(0, -2).Value = "" Then
If Target.Offset(0, 1).Value <> "" Then
Target.Offset(0, -2).Value = Target.Offset(0, 1).Value - Target.Value
End If
Else
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value + Target.Value
End If
Case 6
If Target.Offset(0, -3).Value = "" Then
If Target.Offset(0, -1).Value <> "" Then
Target.Offset(0, -3).Value = Target.Value - Target.Offset(0, -1).Value
End If
ElseIf Target.Offset(0, -1).Value = "" Then
Target.Offset(0, -1).Value = Target.Value - Target.Offset(0, -3).Value
End If
End Select
ExitHere:
Application.EnableEvents = True
End Sub``````

Re: Sum and subtraction

for now I will give up this idea

Bye