2

Here's what I want to achieve:
Here's what I want to achieve

In the example, I manually subtracted the value of the previous cell from the cell.
For example:
For cell C2, I used =A2-A1
For cell C3, I used =A3-A2
For cell C4, I used =A4-A3
And so on.

How do I make this process automatic so that every time I enter a value in the column A, the last cell of a column would automatically get subtracted from new entered value. How do I achieve this?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Um9vdAo
  • 53
  • 2
  • 9

1 Answers1

2

You can create an actual Excel table out of this data. Select the whole "table" and use
Insert -> Table

Then after you enter a new value at the bottom of the table it will become part of the table and the formula in column C will automatically be created.

For this to work you need that column C would have the same formula (can be pasted down). E.g. for me this formula worked:
=IF(ISNUMBER(A1),A2-A1,"")

enter image description here

To make formatting like you want you can change the number format of column C to:
[mm] "Minutes"
Deciding from the image in the question it seems you know how to do it.

ZygD
  • 22,092
  • 39
  • 79
  • 102
  • Worked like a charm! Didn't even have to convert it to table. I couldn't merge some cells after I converted it to a table and your formula is working well without converting it. But there is a slight problem. Take a look at this screenshot: https://i.ibb.co/DRX5TXZ/image.png How do I fix this? – Um9vdAo Dec 20 '20 at 16:11
  • Does 12 AM really represent a new day? – ZygD Dec 20 '20 at 16:14
  • Pls check again. Format it as yyyy-mm-dd so that you will see actual date. My tests show that if it's really another day, then the formula works: https://i.stack.imgur.com/L0OuP.png – ZygD Dec 20 '20 at 16:23
  • Oh, do I have to create a separate column for dates? If I have to, can I merge the cells for the same date, not repeating the same value? – Um9vdAo Dec 20 '20 at 16:27
  • Only for testing purposes, to find out the date which is seen by Excel engine. You *see* time, but actually there is a full date in that cell, which we want to find out. – ZygD Dec 20 '20 at 16:29
  • Added date column, didn't work. Reapplied the formula, same. Changed A to B in the formula and reapplied, same result. https://i.ibb.co/Tk7LXT2/image.png – Um9vdAo Dec 20 '20 at 16:37
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/226200/discussion-between-zygd-and-um9vdao). – ZygD Dec 20 '20 at 16:38