# Calculate percentage difference between dates with formula

#### chilly_bang

##### Board Regular
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

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

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

Is this what you are looking for?
Excel Formula:
``=IF(AND(B3=B2;A3>A2);IFERROR(100-(C3*100)/(C2);0);0)``

#### chilly_bang

##### Board Regular

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

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

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

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

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?

Replies
24
Views
186
Replies
1
Views
60
Replies
1
Views
82
Replies
4
Views
365
Replies
3
Views
178

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.

### Which adblocker are you using?

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

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