Calculating the Usage Between Previous Entry and Current Entry

John Johnson

I have a Smartsheet that I am using to capture the data and voice usage on cellphones on a weekly basis. I would like to be able to calculate the difference between the previous entry for given wireless number and the current entry for the same wireless number. I hope I explain this better below.

Example:

On 9/6 data usage for wireless number 123-456-7890 was 2 and the voice usage was 100

On 9/13 we import more records and the new total usage for 123-456-7890's data changes to 2.5 and voice changes to 125.

On the newest record's row I would like the Data Usage Since Last Week column to populate as 0.5 and the Voice Usage Since Last Week column to populate as 25.

This would reflect that since the previous record the wireless number used 0.5GB more data and 25 more voice minutes.

Is this possible in the same Smartsheet? The Week Number column will automatically record based on the Record Created column. In this example, I had to fudge it so you would be able to see what I would like the final results to look like.

John Johnson

Thank you @Paul Newcome!

I did end up tweaking it a little bit so that if the wireless number only appears once week it will return the current records information as the usage since the last report.

=IFERROR([Peak Minutes]@row - INDEX(COLLECT([Peak Minutes]:[Peak Minutes], [Wireless Number]:[Wireless Number], @cell = [Wireless Number]@row, [Week Number]:[Week Number], @cell = [Week Number]@row - 1), 1), [Peak Minutes]@row)

Paul Newcome

Try something like this:

=[Peak Minutes]@row - INDEX(COLLECT([Peak Minutes]:[Peak Minutes], [Wireless Number]:[Wireless Number], @cell = [Wireless Number]@row, [Week Number]:[Week Number], @cell = [Week Number]@row - 1), 1)

John Johnson

Thank you @Paul Newcome!

I did end up tweaking it a little bit so that if the wireless number only appears once week it will return the current records information as the usage since the last report.

=IFERROR([Peak Minutes]@row - INDEX(COLLECT([Peak Minutes]:[Peak Minutes], [Wireless Number]:[Wireless Number], @cell = [Wireless Number]@row, [Week Number]:[Week Number], @cell = [Week Number]@row - 1), 1), [Peak Minutes]@row)

Paul Newcome

Happy to help. 👍️