Recently, I get an interesting task. We have a database like that:

DateTime | Close |
---|---|

28.09.2009 9:31 | 50.74 |

28.09.2009 9:32 | 50.81 |

28.09.2009 9:33 | 50.8 |

28.09.2009 9:34 | 50.78 |

29.09.2009 9:31 | 50.84 |

29.09.2009 9:32 | 50.8 |

29.09.2009 9:33 | 50.81 |

So we need to find the percent of days from total days where price was MAX. I want to do it without VBA code. So, first of all, we need to restructure our database. I need to get the structure like that.

28.09.2009 | 29.09.2009 | |

9:31 | 50.74 | 50.84 |

9:32 | 50.81 | 50.8 |

9:33 | 50.8 | 50.81 |

9:34 | 50.78 |

Sure, we could use copy/paste and do it manually for each day. However, using Excel’s PivotTables feature, we can avoid this tedious work (especially if we have hundreds of thousands of lines) and let Excel do this work for us. A PivotTable is nothing more than a different, flexible view at your data. A PivotTable combines the best of subtotals, sorting, filtering and rearranging to allow us to see the data in a new view.

In this view we can easy calculate MAX and even count days where we have max value for each minute. We just create the same table and put formula:

28.09.2009 | 29.09.2009 | |

9:31 | =IF(G3=MAX(G$3:G$6);1;0) | 1 |

9:32 | 1 | 0 |

9:33 | 0 | 0 |

9:34 | 0 | 0 |

We search MAX value of the day and if found assign 1, else – 0. Now we can easy calculate the chance of a particular minute in the day being the highest within the day. Just sum by row and divided by the number of days.

Time | Chance |

9:31 | 50% |

9:32 | 50% |

9:33 | 0% |

9:34 | 0% |

As a result, I got a graph: