Get working day of the month with the lowest value-CodePudding

I have a excel spreadsheet with two columns. One with a date and another with the value.

I want to get the working day of the month with the lowest value.

I tried to use a pivot table for it and then group the date but I get a lot of errors.

You can find the spreadsheet here and the sheet name is Historical. The others are just attempts made by me.

=AGGREGATE(15,6,(POWER(10,LOG10(((YEAR(D2)=YEAR($A$2:$A$3254))*(MONTH(D2)=MONTH($A$2:$A$3254)))))*$B$2:$B$3254),1)

and the array formula entered in F2 below is

=INDEX($A$2:$A$3254,MATCH(YEAR(D2)&MONTH(D2)&E2,YEAR($A$2:$A$3254)&MONTH($A$2:$A$3254)&$B$2:$B$3254,0))

CodePudding user response：

I suggest to make an triple nested if-construct that checks if the weekday of the date is a workday, or the date 1 or the day 2. Assuming the date is in cell A4

= if(instr(weekday(A4),”23456”)>0, A4, 
if(instr(weekday(A4 1),”23456”)>0, A4   1, 
if(instr(weekday(A4 2),”23456”)>0, A4   2,”cannot happen”)))

Explanation: one of 3 consecutive days is always a working day. There may be typos since I edit that on iPad without Excel available to test. Weekday returns 1 for Sunday and 7 for Saturday. So 2-6 are workdays.

However with that simple approach you will not detect public holidays on a working day if that is a problem.

Hope I understood you question correctly. One data example with solution would have explained it better.