If statement with multiple text arguments inside range

0
votes

I am trying to find the average of certain values inside a column according to the adjacent (previous column) text value they are paired with. In the following case for example, I would like to find the average of the results scored by the west coast cities. So far this is the formula I've got (according to the example, would be placed in C12). Section in bold represents the section I'm struggling with. Perhaps the entire formula is erroneous?

=AVERAGE(IF( "la" & "sf" & "sea" & "por" within b3:b10 ),c3:c10))

enter image description here

1
excelif-statementaverage

1 Answers

2
votes

Unfortunately there is no WITHIN function in Excel. Fortunately we can use the power of array functions.

Within array context 

=IF($B$3:$B$10={"la","sf","sea","por"}, ...)

will check if the value of $B$3 is equal "la", then true, is equal "sf", then true and so on and then if the value of $B$4 is equal "la", then true, is equal "sf", then true and so on, and so on until $B$10.

So

{=AVERAGE(IF($B$3:$B$10={"la","sf","sea","por"},$C$3:$C$10))}

will do what you want.

Note, that the array literal {"la","sf","sea","por"} has to be a row vector since the $B$3:$B$10 is a column vector.

The formula has to be an array formula. Enter the formula without curly brackets and press [CTRL]+[Shift]+[Enter] at the end. The curly brackets then should appear automatically.

For more flexibility I would list the west coast and east coast states separately somewhere as shown in my example. Note, that the states lists have to be row vectors since the $B$3:$B$10 is a column vector. If this is not possible, TRANSPOSE must be used to transpose them.

enter image description here

To have multiple conditions within the IF in array context, we have to note, that AND() / OR() mostly will not work as expected in array context. To avoid this, we can use following trick. TRUE * TRUE results in 1 but FALSE * TRUE results in 0. So multiplying boolean values (results of comparisons) can be used for AND (all booleans have to be TRUE). TRUE + TRUE results in 2, FALSE + TRUE results in 1, but FALSE + FALSE results in 0. So addition of boolean values (results of comparisons) can be used for OR (at least one boolean has to be TRUE). We should keep in mind, that TRUE + TRUE = 2 which sometimes may not directly the needed result.

So the following will work: enter image description here