Calculate Excel Cell Between Last and Previous Rows

I have an excel sheet that maintains a cost amount for each day. Each day is posted on a new row. I would like to produce the calculation with the difference between the last row (newest amount) and the previous row and write the difference in a new column in the last row. I’m familiar with index and using the for each row, I’m just not sure how to get the previous row data and calculate it with the last row.

The excel formula would be =F16-F15

Hi @pparmly,

Please assign row("Difference_ColumnName")=cdec( DT.Rows.Item(DT.Rows.IndexOf(row)-1)("Amount_ColumnName"))-cdec(row("Amount_ColumnName")) inside the for each row iteration of the datatable.

Warm regards,


If the cost Amount value varies every time, then you have to take the reference first corresponding to the values find which cell exactly where the cost amount value is, suppose you found its “F” and its Cost amount is the last row, then use (datatable.rows.count-1) will give the last index and (datatable.rows.count-2) will give previous index of that.

Datatable.rows(datatable.rows.count-1).item(in which column the value is present) - Datatable.rows(datatable.rows.count-2).item(in which column the value is present)

1 Like