Comparing an array to an array with IF statement in Excel

Question

I am trying to simulate a portfolio rebalancing strategy with Excel.

Assuming there are 10 stocks each with a target allocation weight and an upper and lower band that is acceptable. If the band is exceeded in either direction, rebalancing is mandated. Each stock has a different weight. I am now trying to come up with an if statement to compare the actual allocation weight (after simulated returns) of each stock with its individual upper/lower band.

E3:E13 is the actual allocation weight after returns of a period.

C3:C12 is the lower acceptable value

D3:D12 is the higher acceptable value.

I tried the following formula to populate a cell with either "yes" (= rebalance) or "no" (= not rebalance) based on which the next term allocation will be based:

=IF(E3:E12<$C$3:$C$12,"yes",IF(E3:E12>$D$3:$D$12,"yes","no"))

I was hoping this would compare E3 with C3, E4 with C4 etc, and also E3 with D3, E4 with D4, and so forth. Unfortunately, the formula does not create the right output, it often says "no" when some of the bands are exceeded. How could I fix this?

Do you mean if E3:E12 should match cell by cell with C3:C12? If all values match then show yes otherwise no? — Pankaj Jaju
– Pankaj Jaju, Commented Mar 5, 2014 at 10:43
No, the formula should show "yes" if one or more of the values matche, and "no" if there are no matches. — user3333198
– user3333198, Commented Mar 5, 2014 at 10:48

Pankaj Jaju · Accepted Answer · 2014-03-05 11:46:50Z

2

Something like this? You need to compare a cell's value with 2 other cells, then you can use OR (in case you want to match either) ... or you may use AND (if you want to match both cells)

enter image description here

EDIT: Based on your request - you can use an ArrayFormula to get that (press Ctrl+Shift+Enter)

enter image description here

edited Mar 5, 2014 at 11:46

answered Mar 5, 2014 at 10:57

Pankaj Jaju

5,5112 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3333198 Over a year ago

I need one more step: I would like to have one cell, for example F14 to show "yes" if any of the values in the column C either exceeds its value in the columns D or E.

user3333198 Over a year ago

Great! That's exactly what I was looking for.

Collectives™ on Stack Overflow

Comparing an array to an array with IF statement in Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related