Average & Max formula

[searchingforhelp]

I know how to complete basic Average and Max formulas but what I am trying to accomplish I need assistance with.

1. Finding the Average- Column D:
I am trying to find a specific name (Mike) in column C and find all negative numbers associated with his name in column A and B, and the Average of all the negative numbers identified.

2. Finding the Max- Column E:
I am trying to find a specific name (Mike) in column C and find all negative numbers associated with his name in column A and B, and the Max (for this example it will be -19). I know -19 is not the highest number, it’s the lowest but for this example I would need the formula to identify it as the highest.

Thank you for any help you can provide.

Example 4-27-2021.xlsx
	A	B	C	D	E
1	List one	List two	name	Average	Max
2	-1	7	Mike
3	4	-19	Mike
4	-5	-3	Tim
5	-3	2	Mike
Average & Max

 

[hrayani]

Hi,

Try this in D1 - Is this what you are looking for ??

=(SUMIFS(A2:A5,C2:C5,"Mike",A2:A5,"<0")+SUMIFS(B2:B5,C2:C5,"Mike",B2:B5,"<0"))/(COUNTIFS(C2:C5,"mike",A2:A5,"<0")+COUNTIFS(C2:C5,"Mike",B2:B5,"<0"))

 

[hrayani]

in E1 - Array formula make sure to press CTL+SHIFT+ENT

=MIN(IF(C2:C5="Mike",A2:B5))

 

[searchingforhelp]

Hi Hrayani, thank you! It appears to work . This is great, I would had never figured this out.

 

[hrayani]

Hi Hrayani, thank you! It appears to work . This is great, I would had never figured this out.

You are welcome & thanks for the feedback