Formula needed to find highest total for a given month over the years

LRATOZ

Board Regular
Joined
Aug 17, 2014
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I am still working on a dashboard for my weather data sheet.
I have rainfall data for each month for many years.
I would like to see in the dashboard the highest value for rainfall for each month and in which year it happened.
Something like this (Fictitious data):

JanFebMarAprMayJunJulAugSepOct
15996497413082219658874
2020202020142019201520182018201720192016

I made up a mock spreadsheet with some fictitious data. I had to limit the amount of cells as L2BB only allows for 300 cells maximum. But I hope you will get the gist to see what I mean.
I have listed 5 days of the first three months over three years to limit the amount of data. In real life I got data 365(6) days for each year and it's all in two columns: Dates and Rain gauge readings.
So, the function I'm after will need to calculate the total for each month, then compare the totals for that particular month over the years and then list the sum result for that month and show the year when it happened.
I hope this will make sense as it is not easy for me to put it in proper English (English is not my first language), sorry).
Here's a copy of that code:

2021-06-02 Weather test sheet.xlsx
ABCDEFGH
2The highest total amount of rainfall for:JanFebMar
3The greatest total for the monthValueValueValue
4Happened in this yearYearYearYear
5
6Expected resultsJanFebMar
7423973
8201920202018
91/01/20182
102/01/20184
113/01/20188
124/01/20183
135/01/2018219
141/02/20187
152/02/20182
163/02/20185
174/02/20183
185/02/2018320
191/03/20189
202/03/201819
213/03/201824
224/03/201821
235/03/2018073
241/01/20193
252/01/201912
263/01/20199
274/01/201912
285/01/2019642
291/02/20190
302/02/20190
313/02/20195
324/02/20196
335/02/20191223
341/03/201914
352/03/20190
363/03/201923
374/03/20192
385/03/2019544
391/01/20200
402/01/20200
413/01/20202
424/01/20204
435/01/20202127
441/02/202021
452/02/20201
463/02/202014
474/02/20200
485/02/2020339
491/03/20207
502/03/20206
513/03/20205
524/03/20200
535/03/2020321
Sheet2
Cell Formulas
RangeFormula
C13,C53,C48,C43,C38,C33,C28,C23,C18C13=SUM(B9:B13)
Named Ranges
NameRefers ToCells
Values=Sheet2!$B$9:$B$53C13


Obviously I wont' have the totals for each month in my datasheet as I want to keep the datasheet just for pure data and not for formulas or data manipulation. These totals are just there to give you an indication what the total should be.
I hope somebody can assist with this. I've been racking my brains for many days but I just haven't got enough skills to work this out.
I am using Excel 2016.
Many thanks for your help in advance!
Luke
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,523
Office Version
  1. 365
Platform
  1. Windows
I would like to see in the dashboard the highest value for rainfall for each month and in which year it happened.
What is to happen if the highest value for January occurred equally in 2 or more years? For example ..

21 06 10.xlsm
ABC
91/01/20182
102/01/20184
113/01/20188
124/01/20183
135/01/2018219
141/02/20187
152/02/20182
163/02/20185
174/02/20183
185/02/2018320
191/03/20189
202/03/201819
213/03/201824
224/03/201821
235/03/2018073
241/01/20193
252/01/20190
263/01/20192
274/01/20191
285/01/2019612
291/02/20190
302/02/20190
313/02/20195
324/02/20196
335/02/20191223
341/03/201914
352/03/20190
363/03/201923
374/03/20192
385/03/2019544
391/01/20200
402/01/202013
413/01/20202
424/01/20204
435/01/2020019
441/02/202021
452/02/20201
463/02/202014
474/02/20200
485/02/2020339
491/03/20207
502/03/20206
513/03/20205
524/03/20200
535/03/2020321
Rainfall
Cell Formulas
RangeFormula
C13,C53,C48,C43,C38,C33,C28,C23,C18C13=SUM(B9:B13)
 

LRATOZ

Board Regular
Joined
Aug 17, 2014
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
What is to happen if the highest value for January occurred equally in 2 or more years? For example ..
Yup, that's a possible problem. In that case I would like to show the earliest value!
Thanks for addressing this issue!
Luke
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,523
Office Version
  1. 365
Platform
  1. Windows
In that case I would like to show the earliest value!
OK, try these.

21 06 10.xlsm
ABCDEFGH
1
2JanFebMar
3193973
4201820202018
5
6
7
8
91/01/20182
102/01/20184
113/01/20188
124/01/20183
135/01/2018219
141/02/20187
152/02/20182
163/02/20185
174/02/20183
185/02/2018320
191/03/20189
202/03/201819
213/03/201824
224/03/201821
235/03/2018073
241/01/20193
252/01/20190
263/01/20192
274/01/20191
285/01/2019612
291/02/20190
302/02/20190
313/02/20195
324/02/20196
335/02/20191223
341/03/201914
352/03/20190
363/03/201923
374/03/20192
385/03/2019544
391/01/20200
402/01/202013
413/01/20202
424/01/20204
435/01/2020019
441/02/202021
452/02/20201
463/02/202014
474/02/20200
485/02/2020339
491/03/20207
502/03/20206
513/03/20205
524/03/20200
535/03/2020321
Rainfall
Cell Formulas
RangeFormula
F3:H3F3=AGGREGATE(14,6,$C9:$C53/(TEXT($A9:$A53,"mmm")=F2),1)
F4:H4F4=AGGREGATE(15,6,YEAR($A9:$A53)/(($C$9:$C53=F3)*(TEXT($A9:$A53,"mmm")=F2)),1)
C13,C53,C48,C43,C38,C33,C28,C23,C18C13=SUM(B9:B13)
 

LRATOZ

Board Regular
Joined
Aug 17, 2014
Messages
57
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

OK, try these.
Thanks Peter,
However slight problem:
The datasheet just has raw data in it (To keep it clean).
In my datasheet there's no sum of the values of each month.
I only added the totals for each month so that you don't need to do the calculations yourself.
So, say I want to find the maximum value for the month of January. The formula will need perform following tasks:
-Check for every month of January
-Make the sum of all values in each month of January
-Select the highest value of every month of January and print it.

My datasheet has many columns, but for simplicity reasons I only want to see the formula for the rain gauge.
Once I got a formula I can adapt it to every other dataset

The first column in the datasheet is the date (dd-mm-yy), the next column is Values from the rain gauge for each day.
So far, I've got data going back to 1954 up until today, so, about 22000 entries!
So that's why I want to create a formula that can look up the highest value for the rainfall of each month and then list that value and add the year in which it happened.
My apologies for the convoluted message.
Cheers,

Luke
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,523
Office Version
  1. 365
Platform
  1. Windows
I don't see that you will be able to do this without some sort of helper column(s). Even if it was possible, I think the calculation overhead would be more than with a helper column.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,926
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

See post #10 in
www.mrexcel.com/board/threads/how-to-find-the-maximum-value-in-a-column-based-on-month-and-year.1172458/

I did not enter the rainfall numbers. Since the numbers are not real numbers, assume column C is rain fall
Insert Column E

T202105a.xlsm
ABCDEF
1DateMonthTemperatureMax MoTotal CMax Date
2
31-Jan-2112025683-Jan-21
43-Jan-2112525683-Jan-21
55-Jan-2112325683-Jan-21
62-Feb-21221246719-Feb-21
717-Feb-21222246719-Feb-21
819-Feb-21224246719-Feb-21
94-Mar-2132329766-Mar-21
106-Mar-2132929766-Mar-21
1119-Mar-2132429766-Mar-21
123-Apr-2142532835-Apr-21
135-Apr-2143232835-Apr-21
1418-Apr-2142632835-Apr-21
153-May-21527285518-May-21
1618-May-21528285518-May-21
175-Jan-22131516317-Jan-22
187-Jan-22131616317-Jan-22
192-Feb-22142727642-Feb-22
205-Feb-22141827642-Feb-22
2122-Feb-22141927642-Feb-22
223-Mar-22153140715-Mar-22
235-Mar-22154040715-Mar-22
24
Data
Cell Formulas
RangeFormula
D3:D23D3=AGGREGATE(14,6,$C$3:$C$23/($B$3:$B$23=B3),1)
E3:E23E3=SUMIFS($C$3:$C$23,$B$3:$B$23,B3)
F3:F23F3=SUMPRODUCT(--($B$3:$B$23=B3),--($C$3:$C$23=D3),$A$3:$A$23)
B3:B23B3=MONTH(A3)+(YEAR(A3)-YEAR($A$3))*12


T202105a.xlsm
ABCDEFG
1
2 --- Month ---Max °CDateRain sum
3January1Jan-21253-Jan-2168
4February2Feb-212419-Feb-2167
5March3Mar-21296-Mar-2176
6April4Apr-21325-Apr-2183
7May5May-212818-May-2155
8January13Jan-22167-Jan-2231
9February14Feb-22272-Feb-2264
10March15Mar-22405-Mar-2271
11
12
13
14
15By Month for all yearsMax °CDateRain Min Year
16January253-Jan-21312022
17February272-Feb-22642022
18March405-Mar-22712022
19
Report_a
Cell Formulas
RangeFormula
A3:A10A3=TEXT(C3,"mmmm")
B3:B10B3=MONTH(C3)+(YEAR(C3)-YEAR($C$3))*12
D3:D10D3=INDEX(Data!$D$3:$D$23,MATCH(B3,Data!$B$3:$B$23))
E3:E10E3=INDEX(Data!$F$3:$F$23,MATCH(B3,Data!$B$3:$B$23))
F3:F10F3=LOOKUP(B3,Data!$B$3:$B$23,Data!$E$3:$E$23)
D16:D18D16=AGGREGATE(14,6,$D$3:$D$10/($A$3:$A$10=A16),1)
E16:E18E16=AGGREGATE(14,6,$E$3:$E$10/($D$3:$D$10=D16)*($A$3:$A$10=A16),1)
F16:F18F16=AGGREGATE(15,6,$F$3:$F$10/($A$3:$A$10=A16),1)
G16:G18G16=YEAR(AGGREGATE(15,6,$C$3:$C$10/($F$3:$F$10=F16)*($A$3:$A$10=A16),1))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,926
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Alternative without the monthly summary on the data sheet.

T202105a.xlsm
ABCDEFG
1Report_A
2 --- Month ---Max °CDateRain sum
3January1Jan-21253-Jan-2168
4February2Feb-212419-Feb-2167
5March3Mar-21296-Mar-2176
6April4Apr-21325-Apr-2183
7May5May-212818-May-2155
8January13Jan-22167-Jan-2231
9February14Feb-22272-Feb-2264
10March15Mar-22405-Mar-2271
11
12
13
14
15By Month for all yearsMax °CDateRain Min Year
16January253-Jan-21312022
17February272-Feb-22642022
18March405-Mar-22712022
19
Report_a
Cell Formulas
RangeFormula
F3:F10F3=SUMIFS(Data!$C$3:$C$23,Data!$B$3:$B$23,B3)
F16:F18F16=AGGREGATE(15,6,$F$3:$F$10/($A$3:$A$10=A16),1)
G16:G18G16=YEAR(AGGREGATE(15,6,$C$3:$C$10/($F$3:$F$10=F16)*($A$3:$A$10=A16),1))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,523
Office Version
  1. 365
Platform
  1. Windows
I don't see that you will be able to do this without some sort of helper column
Perhaps I misunderstood your comments about a helper column & you could just go with this in the helper column and use the formulas I suggested above for the monthly maximums and years?
I have hidden quite a few rows for this screen-shot.

21 06 10.xlsm
ABCFGHIJKLMNOPQ
1
2JanFebMarAprMayJunJulAugSepOctNovDec
3359339374345388370370364358370360359
4200319721983199719691994196719962000199419631954
5
6
7
8
91/01/19544 
102/01/195417 
113/01/19541 
124/01/19544 
3426/01/195413 
3527/01/195412 
3628/01/19548 
3729/01/195410 
3830/01/19540 
3931/01/195419258
401/02/195419 
412/02/195413 
423/02/195415 
434/02/195415 
6324/02/19549 
6425/02/19548 
6526/02/19544 
6627/02/19547 
6728/02/19545270
681/03/19547 
692/03/19541 
Rainfall
Cell Formulas
RangeFormula
F3:Q3F3=AGGREGATE(14,6,$C9:$C25000/(TEXT($A9:$A25000,"mmm")=F2),1)
F4:Q4F4=AGGREGATE(15,6,YEAR($A9:$A25000)/(($C$9:$C25000=F3)*(TEXT($A9:$A25000,"mmm")=F2)),1)
C9:C12,C34:C43,C63:C69C9=IF(MONTH(A9)=MONTH(A10),"",SUM(B$9:B9)-SUM(C$8:C8))
 
Solution

LRATOZ

Board Regular
Joined
Aug 17, 2014
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
Perhaps I misunderstood your comments about a helper column & you could just go with this in the helper column and use the formulas I suggested above for the monthly maximums and years?
I have hidden quite a few rows for this screen-shot.
Peter it worked!
This is a great solution!
I checked for nearly every max value in my data collection and it's 100% spot on!
Fantastic job, I'm over the moon with this solution.
Admittingly it requires an extra column for the monthly totals but that's something I can live with.
This function will save me endless hours of sifting through the data.
You are my hero of the day! Thank you very much for your hard work.
Also a big thank you to Dave Patton and Maabadi for their contributions; Much appreciated guys! I wish I could do something in return.
Cheers,

Luke
 

Forum statistics

Threads
1,141,051
Messages
5,703,957
Members
421,321
Latest member
blusky4

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