CODEWITHSUNDEEP
excel-formula
user3333198
user3333198

Reputation: 105

Comparing an array to an array with IF statement in Excel

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?

Upvotes: 0

Views: 4103

Answers (1)

Pankaj Jaju
Pankaj Jaju

Reputation: 5481

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

Upvotes: 2

Related Questions