How to calculate difference keepig in mind first occurences?

[chilly_bang]

I have following data:

• Date (year and month, always the first day, YYYY-MM-DD)
• URL
• Metric (number)

Amount of dates for url could be different: for the first url there could be seven months, for the second url - only five months.

These data should be placed in a table so i can with a formula calculate the prozentual difference of metric between monthes for the certain url.

The following is an example of the table. If you mean, the table should better look otherwise (transponded, sorted on the other way or so) to enable smart calculation - just say!

+------------+------+--------+----------------------------------+
|      A     |   B  |    C   |                 D                |
+------------+------+--------+----------------------------------+
| Date       | URL  | Metric | Metric: Change to previous month |
+------------+------+--------+----------------------------------+
| 2019-06-01 | url1 | 1312   |                                  |
+------------+------+--------+----------------------------------+
| 2019-07-01 | url1 | 1241   |                                  |
+------------+------+--------+----------------------------------+
| 2019-08-01 | url1 | 5370   |                                  |
+------------+------+--------+----------------------------------+
| 2019-09-01 | url1 | 6117   |                                  |
+------------+------+--------+----------------------------------+
| 2019-10-01 | url1 | 5827   |                                  |
+------------+------+--------+----------------------------------+
| 2019-11-01 | url1 | 4827   |                                  |
+------------+------+--------+----------------------------------+
| 2019-12-01 | url1 | 5267   |                                  |
+------------+------+--------+----------------------------------+
| 2020-01-01 | url2 | 7218   |                                  |
+------------+------+--------+----------------------------------+
| 2020-02-01 | url2 | 6440   |                                  |
+------------+------+--------+----------------------------------+
| 2020-03-01 | url2 | 5966   |                                  |
+------------+------+--------+----------------------------------+
| 2020-04-01 | url2 | 5283   |                                  |
+------------+------+--------+----------------------------------+
| 2020-05-01 | url2 | 6020   |                                  |
+------------+------+--------+----------------------------------+

I firstly thought, i calculate the difference of the secod month to the first with the formula like =c3/c2-1 and get the procentual difference. But then i realized two problems:

1. the first month should be excluded from calculation because there is nothing, what can be compared with the first month's value.
2. there are multiple urls, and the formula must "know", that calculations should appear only applied to same url's metrics.

How can i adjust the original formula and calculate changes to previous months while getting into consideration these two factors?

 

[jmacleary]

Hello. So long as your data is sorted as you have shown, i.e. column a is ascending date for the url in column B then this will work for you (I have assumed your data starts in A1)

Book1
	A	B	C	D
1	Date	URL	Metric	Metric: Change to previous month
2	2019-06-01	url1	1312
3	2019-07-01	url1	1241	71
4	2019-08-01	url1	5370	-4129
5	2019-09-01	url1	6117	-747
6	2019-10-01	url1	5827	290
7	2019-11-01	url1	4827	1000
8	2019-12-01	url1	5267	-440
9	2020-01-01	url2	7218
10	2020-02-01	url2	6440	778
11	2020-03-01	url2	5966	474
12	2020-04-01	url2	5283	683
13	2020-05-01	url2	6020	-737
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=IF(B2<>B1,"",C1-C2)

 

[chilly_bang]

Thank you! It works: if b3 not equals b2, write nothing, otherwise do calculation... With Excel everyday is something new very handy operator `<>`.