Sum and subtraction

[Ramsousa1986]

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

[#2]

@Ramsousa1986 

That would result in circular reference, as explained here: 

https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-b...

[#3]

@Ramsousa1986 

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?

[#4]

@Twifoo 

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

[#5]

@Ramsousa1986 

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.

[#6]

@adversi and the rest people....

So, let me try explain again...

This is my Spreadsheet

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!

[#7]

@Ramsousa1986 

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?

[#8]

@Ramsousa1986 

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.

[#9]

@adversi 

"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

[#10]

@Ramsousa1986 

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

[#11]

@Hans Vogelaar 

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

[#12]

No. Is a paper!

[#13]

@Ramsousa1986 

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

[#14]

@adversi 

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)

[#15]

@Ramsousa1986 

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.

[#16]

@adversi 

I gess i need to use VBA 

[#17]

@Ramsousa1986 

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.

[#18]

@Ramsousa1986 

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

[#19]

@Hans Vogelaar 

@adversi 

@Twifoo 

Thank you for your help

for now I will give up this idea

Bye