Calculate percentage difference between dates with formula

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
54
I have a table, like this:

Rich (BB code):
+------------+--------------+--------+
| Week       | Search query | Clicks |
+------------+--------------+--------+
| 2020-11-09 | keyword 1    | 3      |
+------------+--------------+--------+
| 2020-11-16 | keyword 1    | 3      |
+------------+--------------+--------+
| 2020-11-09 | keyword 2    | 7      |
+------------+--------------+--------+
| 2020-11-16 | keyword 2    | 14     |
+------------+--------------+--------+

How can i calculate the percentage difference of clicks between the latest and earliest date with a formula?

Anyhow like this: IF B3=(B3-1) & A3>(A3-1) → iferror(100-(c3*100/(C3-1));0)

I've tried it with pivot - it works, but after data refresh my pivot gets broken.
 
Last edited by a moderator:

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
54
For example, in the first two records for "abmahnschutz", how do you get to "100" from those two records?

Not 100 - but 50.

This is the original:

Code:
+------------+-----------------------------------------------------+--------+
| Week       | Search query                                        | Clicks |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | abmahnschutz                                        | 4      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahnschutz                                        | 8      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahnschutz versicherung                           | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahn versicherung                                 | 3      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | ambulante zusatzversicherung ohne gesundheitsfragen | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | ambulante zusatzversicherung ohne wartezeit         | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | app drive x                                         | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | autoversicherung                                    | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | autoversicherung                                    | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | autoversicherungen                                  | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | bauzeitversicherung                                 | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | beamte altersvorsorge                               | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | beitragsentlastung                                  | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | beitragsentlastungstarif                            | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | beitragsentlastungstarif                            | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | betriebsunterbrechungsversicherung                  | 3      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | betriebsunterbrechungsversicherung                  | 10     |
+------------+-----------------------------------------------------+--------+

If i display the original table with dates as columns, so it looks like:

Code:
+-----------------------------------------------------+------------+------------+
| Search query                                        | 09.11.2020 | 16.11.2020 |
+-----------------------------------------------------+------------+------------+
| abmahn versicherung                                 |            | 3          |
+-----------------------------------------------------+------------+------------+
| abmahnschutz                                        | 4          | 8          |
+-----------------------------------------------------+------------+------------+
| abmahnschutz versicherung                           |            | 2          |
+-----------------------------------------------------+------------+------------+
| ambulante zusatzversicherung ohne gesundheitsfragen |            | 2          |
+-----------------------------------------------------+------------+------------+
| ambulante zusatzversicherung ohne wartezeit         | 2          |            |
+-----------------------------------------------------+------------+------------+
| app drive x                                         |            | 2          |
+-----------------------------------------------------+------------+------------+
| autoversicherung                                    | 6          | 6          |
+-----------------------------------------------------+------------+------------+
| autoversicherungen                                  | 2          |            |
+-----------------------------------------------------+------------+------------+
| bauzeitversicherung                                 | 6          |            |
+-----------------------------------------------------+------------+------------+
| beamte altersvorsorge                               |            | 2          |
+-----------------------------------------------------+------------+------------+
| beitragsentlastung                                  | 2          |            |
+-----------------------------------------------------+------------+------------+
| beitragsentlastungstarif                            | 2          | 2          |
+-----------------------------------------------------+------------+------------+
| betriebsunterbrechungsversicherung                  | 3          | 10         |
+-----------------------------------------------------+------------+------------+

So the date difference will be calculated with =IFERROR(100-(B2*100)/(C2);0) and the final result i want will look like:

Code:
+-------------------------------------------------------+--------------------------------+
| Search query                                          | Click Difference between dates |
+-------------------------------------------------------+--------------------------------+
| abmahn versicherung                                   | 100                            |
+-------------------------------------------------------+--------------------------------+
| abmahnschutz                                          | 50                             |
+-------------------------------------------------------+--------------------------------+
| abmahnschutz versicherung                             | 100                            |
+-------------------------------------------------------+--------------------------------+
| ambulante zusatzversicherung ohne   gesundheitsfragen | 100                            |
+-------------------------------------------------------+--------------------------------+
| ambulante zusatzversicherung ohne   wartezeit         | 0                              |
+-------------------------------------------------------+--------------------------------+
| app drive x                                           | 100                            |
+-------------------------------------------------------+--------------------------------+
| autoversicherung                                      | 0                              |
+-------------------------------------------------------+--------------------------------+
| autoversicherungen                                    | 0                              |
+-------------------------------------------------------+--------------------------------+
| bauzeitversicherung                                   | 0                              |
+-------------------------------------------------------+--------------------------------+
| beamte altersvorsorge                                 | 100                            |
+-------------------------------------------------------+--------------------------------+
| beitragsentlastung                                    | 0                              |
+-------------------------------------------------------+--------------------------------+
| beitragsentlastungstarif                              | 0                              |
+-------------------------------------------------------+--------------------------------+
| betriebsunterbrechungsversicherung                    | 70                             |
+-------------------------------------------------------+--------------------------------+

So how i come from the original table to the wanted result with a formula?

I'm very bad explainer. @Joe4 you are the hero on your trying to understand me.
 
Last edited:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Do you only have the two dates?
If so, then why not just use the original data table, where you have two columns of dates?
Then, you could just put this formula in, which would only return a value if both date columns are populated for that row:
Excel Formula:
=IF(AND(B2<>"";C2<>"");100-(B2*100)/C2;"")
 

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
54
Yes, there are always only two dates. Sure, your last method would be very handy but my original table doesn't have two columns for dates - in original table dates are all in the same column.

I add a file, where original table is located in the first tab, , the tab "Pivot" contains pivoted original data and calculation of click difference between dates, and the third tab "result" contains the table i want to achieve without pivot. Here is the example file.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
I do not know of a good approach without using VBA. I rarely use Pivots myself, and would tend to use Micosoft Access to do this (as I am much better with Microsoft Access queries than with Pivots).

In Excel, I would probably use VBA to change the structure of your data to look like the two-column date layout that you showed.
If that is an acceptable approach to you, let me know, and I can come up with VBA code do that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top