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:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
I am not really sure I completely follow your question, but I do see a problem here:
Rich (BB code):
IF B3=(B3-1) & A3>(A3-1) → iferror(100-(c3*100/(C3-1));0)
It is mathematically impossible for any value to be equal to itself minus 1.
Do you really mean:
IF B3=B2...?
 

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
54
yes, sure, absolutely. Thats just a pseudo code to demonstrate conditions: keywords should be same and the second date should be later as the previous.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Is this what you are looking for?
Excel Formula:
=IF(AND(B3=B2;A3>A2);IFERROR(100-(C3*100)/(C2);0);0)
 
Solution

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
54

ADVERTISEMENT

Great! How is it possible to add into neighbour column the corresponding keyword? So i have in column D the keyword und in column F - according difference?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
How is it possible to add into neighbour column the corresponding keyword? So i have in column D the keyword und in column F - according difference?
I do not understand.
Can you walk me through an actual example?
 

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
54

ADVERTISEMENT

This is the original table:

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     |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | deliktunfähige kinder                               | 4      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | deliktunfähige kinder                               | 4      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | deliktunfähige kinder haftpflicht                   | 3      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | deliktunfähige kinder wer haftet                    | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | diensthandy kaputt                                  | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | diensthandy kaputt wer zahlt                        | 4      |
+------------+-----------------------------------------------------+--------+

What i want to achieve is a table where i have:

Code:
+-----------+----------------------+
| Keywords  | Date Difference in % |
+-----------+----------------------+
| keyword 1 | number               |
+-----------+----------------------+
| keyword 2 | number               |
+-----------+----------------------+

Beside of this, as you can see in the original table, there are keywords existing only for one of both dates - for earlier or later date. I want that the formula workouts these cases like:
  • keyword exists for earlier date - date difference is -100%
  • keyword exists for the later date - date difference is 100%
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Please show me the expected results, based on your data example (that is what I mean when I ask for an example).
 

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
54
For these rows of original table:

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      |

i would like to get this result:

Code:
+-----------------------------------------------------+-----------------+
| Search query                                        | Date Difference |
+-----------------------------------------------------+-----------------+
| abmahnschutz                                        | 50              |
+-----------------------------------------------------+-----------------+
| abmahnschutz versicherung                           | 100             |
+-----------------------------------------------------+-----------------+
| abmahn versicherung                                 | 100             |
+-----------------------------------------------------+-----------------+
| ambulante zusatzversicherung ohne gesundheitsfragen | 100             |
+-----------------------------------------------------+-----------------+
| ambulante zusatzversicherung ohne wartezeit         | -100            |
+-----------------------------------------------------+-----------------+
| app drive x                                         | 100             |
+-----------------------------------------------------+-----------------+
| autoversicherung                                    | 0               |
+-----------------------------------------------------+-----------------+
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Can you explain the logic for determining the "Date Difference" value?

For example, in the first two records for "abmahnschutz", how do you get to "100" from those two records?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

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