Cannot find Max/Min/Average based on multiple criteria

**EXcelForumGuy** · 01-04-2023, 02:35 PM

Hello,

I have been trying for several days to find the min, max, and average of ammonia levels in various ponds. I need the search to include those labelled "pond" and "duplicate" (they have different names in the actual data set). I have tried the following formula to no avail:

=MINIFS(B13:DZ13,B9:DZ9,B9,B8:DZ8,E8)

I suspect the issue comes from querying the same data range for two separate entities is causing the issue, despite separating (duplicating) it into "Sample ID" and "SampleID2" as the formula seems to work quite well if I change the second query to date rather than the original dat range again.

**Glenn Kennedy** · 01-04-2023, 02:45 PM

EITHER... you haven't told us something important... or you are overcomplicating this...
Why not:
=MIN(B13:E13)

etc...

**EXcelForumGuy** · 01-04-2023, 02:51 PM

Sorry for leaving that out, I need this because the actual data set is far larger than what I included and I need to repeat this for 50 ponds with a data set that grows annually. The end goal is to create a sheet that would calculate these automatically without the need to manually select the data range for each pond. The pond names do not change year to year so thats why I am trying to have the formula query for the name of the pond (Pond and Duplicate in the dataset) the actual pond names are alphanumeric values delineating the watershed and ID within the watershed. thank you for the help, I hope this makes it clearer.

**Glenn Kennedy** · 01-04-2023, 02:56 PM

Nope. Can you upload a more realistic sample sheet, if this isn't it:

=LET(A,$13:$13,MIN(DROP(FILTER(A,A<>""),,1)))

**EXcelForumGuy** · 01-04-2023, 03:03 PM

Thanks Glenn, let me try that formula out in my document then I will let you know. The issue with uploading a bigger workbook is that it contains a lot of doxxable/confidential information and It may take me a lot of time to scrub it completely. Either way I appreciate the help.

**EXcelForumGuy** · 01-05-2023, 08:41 AM

Okay I have scrubbed the file and now it is ready to post. I need the formula to find the minimum amount of NH4 between Pond 1 with Duplicate 1, and the same formula to find the minimum between Pond 2 and duplicate 2, and the same for Pond 3 and Duplicate 3. Thanks again for all the help, let me know if there is anything else I should post.

**Glenn Kennedy** · 01-05-2023, 08:48 AM

Is that ONE number for minimum of P1/D1, ONE number for minimum of P2/D2, etc...

or ONE number, overall?

**Glenn Kennedy** · 01-05-2023, 08:57 AM

Also... minimum DETECTED amount (0.04) or <0.03 for Pond 2?

Expected results would help!!

**Glenn Kennedy** · 01-05-2023, 08:58 AM

How should <0.03 be treated when calculating averages?

**davsth** · 01-05-2023, 09:19 AM

I agree with Glenn an expected result would be nice. Also the text in with numbers creates problems.

Not being as smart as Glenn

min (minifs,minifs) would get you the minimum

=min(MINIFS(B12:E12,B8:E8,D8),MINIFS(B12:E12,B8:E8,e8)) perhaps

**EXcelForumGuy** · 01-05-2023, 09:41 AM

1 minimum for P1/D1; 1 minimum for P2/D2. Ive been instructed to find the minimum detected amount, and the treat the <0.03 as 0.03. I know it creates problems for averages but we have to assume its at the largest point.

**EXcelForumGuy** · 01-05-2023, 10:47 AM

thank you, this is my first post. Still learning what is required for responders.

**Glenn Kennedy** · 01-05-2023, 11:00 AM

Three formulae. They all resemble this one. See file.

=LET(A,$B$8:$N$8,B,SUBSTITUTE($B$12:$N$12,"<","")+0,C,COUNTIF(A,$B15)+1,MIN(INDEX(B,,MATCH($B15,A,0)+SEQUENCE(C,,0))))

copied down.

**EXcelForumGuy** · 01-05-2023, 04:13 PM

Thank you, I will mark this one solved. This helped me out immensely.

**Glenn Kennedy** · 01-05-2023, 04:22 PM

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Cannot find Max/Min/Average based on multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Re: Cannot find Max/Min/Average based on multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find Out Average based on different criteria

Average Multiple Rows in Multiple Columns Based off Multiple Criteria Points

Find average of last 24 hrs data from a filtered range based on a criteria

Trying to find median & average for multiple columns with multiple criteria

Trying to find median & average for multiple columns with multiple criteria

[SOLVED] Need to find an average based on several criteria.

[SOLVED] If any combinations of multiple criteria met, then find average time elapsed

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions