Calculate percentage difference between dates with formula

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
57
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:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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...?
 
Upvote 0
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.
 
Upvote 0
Is this what you are looking for?
Excel Formula:
=IF(AND(B3=B2;A3>A2);IFERROR(100-(C3*100)/(C2);0);0)
 
Upvote 0
Solution
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?
 
Upvote 0
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?
 
Upvote 0
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%
 
Upvote 0
Please show me the expected results, based on your data example (that is what I mean when I ask for an example).
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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
Back
Top