Excel, Differences within Day for Multiple Dates

1

1

Im currently facing a problem trying to find variations in a Database as follows:

>   Date   | Hour | Place 1 | Place 2 | Place 3 ... Place K (about 50)
>-------------------------------------------------------------
>1-1-2013  |  1   |  654    |  842    |  326    ...   842
>1-1-2013  |  2   |  847    |  247    |  625    ...   874
>1-1-2013  |  3   |  648    |  321    |  547    ...   624
>   ...    | ...  |  ...    |  ...    |  ...    ...   ...
>1-1-2013  |  24  |  748    |  654    |  548    ...   743
>2-1-2013  |  1   |  648    |  548    |  624    ...   845
>2-1-2013  |  2   |  654    |  348    |  247    ...   254
>   ...    |  ... |  ...    |   ...   |  ...    ...   ...

Dates as Day/Month/Year

What I need is a way to find the difference between the max and min value for each day for each place.

Either in one formula or using an aux sheet.

Im guessing some sort of Max(If and Min(If but I haven't been able to figure it out.

After getting those differences I need to know which are the top 5 biggest ones per day. (I know how to get the top 1, but I have no idea how to get the other 4).

Any help would be appreciated.

Vicente Gre

Posted 2015-08-17T19:20:08.240

Reputation: 63

"which are the top 5 biggest ones per day" - how do you want to manage duplicates (e.g. 842 is twice in 1st hour for 1st day)? Do you want to list them twice (third and fourth biggest values are both 842), or only once? How often do you need to run this task? Do you append data below your range or it's always a new sheet? – Máté Juhász – 2015-08-18T12:35:50.450

Sorry, I didn't make myself clear, I meant the biggest differences. And duplicates should be listed twice. I'll need to run this on around 3 years of data ( so 336524=26.280 rows ). All the data will be in the same sheet, the outcome can be on another. – Vicente Gre – 2015-08-18T19:26:47.760

Answers

1

=MAX(IF($A$2:$A$26281=$A2,C$2:C$26281,""))-MIN(IF($A$2:$A$26281=$A2,C$2:C$26281,""))

Will find the difference between the smallest and largest value for a given place on a given day. Enter as an array formula by confirming with ctrl+shift+enter. Assuming your data starts at A1 and doesn't have any blank rows or columns, you can enter this formula for Place 1, autofill over to Place k, then autofill down. It will probably take forever to calculate.

You can use LARGE() to find the top five biggest values from that.

Kyle

Posted 2015-08-17T19:20:08.240

Reputation: 2 286

What should I put between "" ? And where should I place this formula? Im trying to use it by placing it a few columns after K and writing the place name between "" but Im getting a wrong number. – Vicente Gre – 2015-08-20T20:52:57.627

1@VicenteGre The formula is designed such that you put it in row two after place k. If you autofill it down it will tell you all the differences for Place 1, so you can autofill across to also get the data for places 2-k. Do not put anything between the quotes, it's that way on purpose. – Kyle – 2015-08-20T21:10:57.613

Asked: 2015-08-17T19:20:08.240

Viewed: 75 times

Active: 2015-08-18T21:09:46.567