# Problem with Min and Max functions

1. ## Problem with Min and Max functions

I have a pretty involved syntax:

=SUMPRODUCT((\$H\$16:\$H\$838<=140)*(\$H\$16:\$H\$838>=70)*(\$M\$16:\$M\$838="Interval 1")*(\$F\$16:\$F\$838=(MIN(\$F\$1:\$F\$5000)))*\$N\$16:\$N\$838)

The problem I am having is with the part of the program (\$F\$16:\$F\$838=(MIN(\$F\$1:\$F\$5000))). The min function in this case refers to days, where I had renamed/recoded Mon through Sunday with numbers (Sun = 0, Mon = 1 and so on). Now I have a problem, I have to enter data based on the day, but the first day doesn't necessarily correspond with the coded day.

For example, for one set of data ( I have to copy this formula across many different files and the 1st day are not the same between files) the coded day/renamed days however are still Sun=0, Mon=1 and so on.

First Day Mon
Second Day Tue
third Day Wed

But for another file

First Day Sat
Second Day Sun
Third Day Mon

I hope you get the idea.

Originally I had it set up where it was taking the lowest number and then the second lowest and so on, which worked well. However, then I found out that Saturday and Sunday were also included and that no longer works because if one data files Day 1 is Sat, which is coded by me as 6, then I dont know how to make it say that Sun (coded as 0) is day 2.

I know that was long winded but i really could use any help I can get since I have been trying to figure this out for 2 days now.  Register To Reply

2. ## re: Problem with Min and Max functions

How did you
I had renamed/recoded Mon through Sunday with numbers (Sun = 0, Mon = 1 and so on)
Did you Find/Repace all these?
Is VBA an option or would you like Excel Functions ONLY?  Register To Reply

3. Excel only pls, and what I had done was the days are listed so I made a new column that read:

=IF(C13="Sun",0,IF(C13="Mon",1,IF(C13="Tue",2,IF(C13="Wed",3,
IF(C13="Thu",4,IF(C13="Fri",5,IF(C13="Sat",6)))))))

The formula I posted in the original post refers to this column for the value of the day. However, I like I said before, Day 0 is always Sunday and Day 1 monday and so on, but each file only has three days worth of data, some that start on Mon others on Sat.

I want the final result to look like:

First Day of Data #
Second Day of Data #
Third Day of Data #

But depending on the file, the first day can be any day of the week. So i figured I turn all the days into numbers and do it that way. I works fine for those files where the first day is toward the beginning of the week,because I can use the min function and go from there. But when the first day is Saturday then I am unable to tell it that day 3 is going to be 1, monday. So I need it to recognize that it starts from day one whatever that day maybe, and then just cycles through the days till there are no more days present to analyze.

Is there a way to tell excel to start at what ever day is first in the file, and then cycle through sequentially. If it happens to start at 5 or 6 (Fri or Sat) that it just go back to zero for the third day?  Register To Reply

4. I had come up with this so far:

=SUMPRODUCT((\$H\$16:\$H\$838<=140)*(\$H\$16:\$H\$838>=70)*(\$M\$16:\$M\$838="Interval 1")*(\$F\$16:\$F\$838=(IF(\$F\$2+1=7, \$F\$2:\$F\$5000=0, \$F\$2:\$F\$5000=\$F\$2+1)))*\$N\$16:\$N\$838)

But it doesnt seem to work  Register To Reply