Forum Discussion

Ramsousa1986's avatar
Ramsousa1986
Copper Contributor
Dec 06, 2020

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 ...
excel
Formulas and Functions
Macros and VBA
adversi's avatar
adversi
Iron Contributor
Dec 06, 2020

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.

  • Ramsousa1986's avatar
    Ramsousa1986
    Copper Contributor
    Dec 06, 2020

    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!

     

     

    • adversi's avatar
      adversi
      Iron Contributor
      Dec 06, 2020

      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.

      • Ramsousa1986's avatar
        Ramsousa1986
        Copper Contributor
        Dec 06, 2020

        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

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 06, 2020

      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?

      • Ramsousa1986's avatar
        Ramsousa1986
        Copper Contributor
        Dec 06, 2020

        HansVogelaar 

         

        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

         

         

Resources