Average & Min Function Problem in Array Formula

I've tried on it a hard but still unable to get solution ....

Actually with the help of array formula I"m taking out Average, Min, Max value of ranges and I was omitting zero but the problem is occurring that if i have originally zero in my ranges then Average & Minimum function is not working properly. In attached sheet i've defined problems in a very specific manner ........

mubashir, if you're saying you don't want 0 to be excluded then remove the * by E from the IF

C20: =MIN(IF($C$4:$C$12=$B$19,$E$4:$E$12))
CSE

C21: =MAX(($C$4:$C$12=$B$19)*($E$4:$E$12))
CSE

C22: =SUMIF($C$4:$C$12,$B$19,$E$4:$E$12)/COUNTIF($C$4:$C$12,$B$19)

C23: =AVERAGE(IF($C$4:$C$12=$B$19,$E$4:$E$12))
CSE

You can see I removed the unnecessary unary operators given the * will automatically coerce the Booleans.

That said I don't understand the "True" Average results... can you explain how you arrived at 0.52/0.65 for Stress/Layout respectively ?

Originally Posted by DonkeyOte

That said I don't understand the "True" Average results... can you explain how you arrived at 0.52/0.65 for Stress/Layout respectively ?

Sorry my mistake actually as in cell F15 I wrongly inputted F15=(E4+E8+E9+E12)/5 which should be 4 …. i manually inputted stress / layout values in True formulas .........

You can see I removed the unnecessary unary operators given the * will automatically coerce the Booleans.

About unary operators now my concept is more clear actually I was getting confused while formula auditing procedures so I was mixing Boolean "False" & 0 values and I think I've still some confusion about False & 0 ……

Anyway Thanks DK for your valuable advise ......

The -- is used to coerce something to a number ... but it represents only one of a whole variety of methods to coerce numbers stored as text / logicals to numbers, consider:

A1: "10" (" represents text)

B1: =--A1 --> 10
B1: =0+A1 --> 10
B1 =1*A1 --> 10
B1: =A1/1 --> 10

All achieve the same result... the reason -- is used in majority of cases is that it is argued to be the most efficient (ignoring issue of single unary for now (quicker still but open to error)) ... however where you are obliged to use one of the other coercion methods there is thus no need to use double unary... so continuing the above example of A1 being "10", if B1 was to be equal to A1 multiplied by value of C1 then there would be no point in using:

B1: =--A1*C1

given as we have demonstrated already the multiplication will coerce A1 anyway so we can just use:

B1: =A1*C1

the same would be true if we wanted to add C1 to A1, divide the two values etc etc

B1: =A1/C1
B1: =A1+C1

I hope that clears the waters a little... it can take a while to get the hang of it and everyone here will post the odd formula with unnecessary coercion as it's easy to forget - especially when adapting formulae from one version to another etc...

DK thx for your clarification and sorry I’m just questioning like a dumb student but I really want to get knowledge …. So here is what I’ve got ……… see in this formula I used * and it worked fine and I guessed how it worked

C4=Stress; C5=Layout; C6=Layout; C7=Layout; C8=Stress; C9=Stress; C10=Layout; C11=Layout; C12=Stress

E4=1; E5=2; E6=3; E7=4; E8=5; E9=6; E10=7; E11=9; E12=8;

Here is the process start;

=MAX(($C$4:$C$12=”Stress”) * ($E$4:$E$12))
=MAX ({“Stress”;”Layout”;”Layout”; ”Layout”; “Stress”; “Stress”; ”Layout”; ”Layout”; “Stress”}*($E$4:$E$12)
=MAX({“True”;”False”;”False”; ”False”; “True”; “True”; ”False”; ”False”; “True”}* ($E$4:$E$12)
=MAX({“True”;”False”;”False”; ”False”; “True”; “True”; ”False”; ”False”; “True”}*({1;2;3;4;5;6;7;9;8})

Now (True*1=1 ; False*2=0)
=MAX({1;0;0;0;5;6;0;0;8})
Result is 8 which is true and i'm clear about it

Now I’m trying to show the process of , which is still unclear for me …..

=MAX(($C$4:$C$12=$B$19) , ($E$4:$E$12))
=MAX ({“Stress”;”Layout”;”Layout”; ”Layout”; “Stress”; “Stress”; ”Layout”; ”Layout”; “Stress”}, ($E$4:$E$12)
=MAX({“True”;”False”;”False”; ”False”; “True”; “True”; ”False”; ”False”; “True”}, ($E$4:$E$12)
=MAX({“True”;”False”;”False”; ”False”; “True”; “True”; ”False”; ”False”; “True”} , ({1;2;3;4;5;6;7;9;8})
Result is 9 but i don't know how ????? Please just explain me what’s going on here …..

I know I must use If condition or anything else but I just want to clear that issue of , as I’m completely stumped ……

@DK I’m sorry as this thread is seems to be Tutorial and you must be thinking that why you tried to learn me something just kidding

In the latter example where you're returning 9:

Please Login or Register  to view this content.

In this case you are no longer multiplying E by the Logical output of the test so you essentially end up with 2 arrays of values in the MAX function, ie:

Please Login or Register  to view this content.

(consider it the same as saying =MAX(A1:A9,C1:C9))

and given those range of values the MAX value is 9... the Logical array will be completely ignored as is it contains no numbers and the Max number in the entire range of values is 9.

In order for the numerical values to be ignored where C4:C12 <> B19 you need to adopt an approach of either:

a) multiplying the Boolean result with the numerical value as you did before (such that FALSE leads to a 0 value in the resulting Array of values):

Please Login or Register  to view this content.

or

b) use an IF such that the array is populated only with numbers where test returns TRUE (probably the more common approach)

Please Login or Register  to view this content.

Using your example the IF approach would generate:

Please Login or Register  to view this content.

The MAX of which is 8 - as we saw with the approach returning 9 the Logicals will be ignored completely by the MAX function.

I think also re:

=MAX({TRUE,FALSE,TRUE},{2,2,3})

and expecting

=MAX({2,0,3})

you're confusing this function with SUMPRODUCT... SUMPRODUCT is an altogether different function as your multiplying arrays of values

=SUMPRODUCT(array1,array2)

is the same as saying

=SUMPRODUCT(array1*array2)

this is not the case in a MAX function... it's simply how SUMPRODUCT is designed.

=MAX(array1,array2)

is just that - the MAX of both arrays of values ... not the max of those arrays once multiplied...

=MAX(SUMPRODUCT(array1,array2))

would do that but there's obviously no need to use SUMPRODUCT here so we would just use:

=MAX(array1*array2) or MAX(IF(array1=test,array2))
CSE

Originally Posted by donkeyote

in the latter example where you're returning 9:

Please Login or Register  to view this content.

in this case you are no longer multiplying e by the logical output of the test so you essentially end up with 2 arrays of values in the max function, ie:

Please Login or Register  to view this content.

(consider it the same as saying =max(a1:a9,c1:c9))
and given those range of values the max value is 9... The logical array will be completely ignored as is it contains no numbers and the max number in the entire range of values is 9.

This is the thing i need to clarify and now its clear ...thank you very much for your concentration and patience .......

Originally Posted by DonkeyOte

I think also re:

=MAX({TRUE,FALSE,TRUE},{2,2,3})

and expecting

=MAX({2,0,3})

you're confusing this function with SUMPRODUCT... SUMPRODUCT is an altogether different function as your multiplying arrays of values

=SUMPRODUCT(array1,array2)

is the same as saying

=SUMPRODUCT(array1*array2)

Oh its simply marvelous , I didn't check that you sent another explanation .... Now I think i'm Crystal Clear on this issue and yes you are right i was confusing this function with SUMPRODUCT and now i'm getting clear on sum product as well especially use of unary in case of , or use of * sign ..... thank a lot DK ......