View original (French)

Empty cell if = cell above

Solved
alex23160 -  
Hello,

I would like to create a very succinct account tracking table in Excel.
I have created an automatic formula that allows me to only enter the values of my expenses and my income, and Excel calculates my total (see attached image):

http://image.noelshack.com/fichiers/2015/53/1451294056-excel-compte-1.png

Following that, I copied the formula by dragging the cell down, and I get this:

http://image.noelshack.com/fichiers/2015/53/1451294202-excel-compte-2.png

Everything is normal.

Now, I would like the cell (in the "remaining" column) that is equal to the cell above to be empty (or colored white with white text) to avoid having the same cell multiple times in a row.

If you have a tip, I would appreciate it.

Thank you :)

Configuration: Windows 7 / Chrome 47.0.2526.106

2 answers

  1.  PHILOU10120 Contributor
    Hello

    You can use a formula with controls

    =IF(AND(E6="",F6=""),"", $G$5+SUM($E$5:E6)-SUM($F$5:F6))

    --
    It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from their mistakes.
    1
    1. alex23160 Author
      Hello,

      thank you for the response, by copying the formula letter by letter I get the desired result.

      Could you explain how the formula works please?
      I would like to use it again in other spreadsheets.
      0
    2. PHILOU10120 Contributor
      @alex23160We check if E6 and F6 are empty, and in that case, we write nothing, hence the ""
      Then we take the starting value in G5 + the sum of the exits in column E - the sum in column F.
      0
    3. alex23160 Author
      Thank you for the explanations, I modified your formula in the "if false" part so that the formula is a bit like the one I created at the beginning.

      Here is my final formula:

      =IF(AND(E6="",F6=""),"",G5-E6+F6)

      If E6 and F6 are empty, then the case is empty; if E6 and F6 are filled, then the formula G5-H6+F6 applies.

      Thank you for your help PHILOU! :)
      0
    4. PHILOU10120 Contributor
      @alex23160Yes, but your formula will display #N/A
      """+ does not work
      0
    5. alex23160 Author
      My formula works very well, here is a screenshot of my Excel table:

      http://image.noelshack.com/fichiers/2015/53/1451308041-excel-2.png

      http://image.noelshack.com/fichiers/2015/53/1451308157-excel-3.png
      0