# If, then formula problem?

#### PeetsDrinker

##### New Member
I’m having a hard time with what I think may have a simple solution; however, I’m not sure if I’m using the right formula(s) for what I’m trying to accomplish.

I have the following columns:
C: Month (Dec 2007 – Dec 2008)
D: Month-end closing price
E: Change in price
F: Decision to hold “H” or sell “S”
G: Decision to hold “H” or buy “B”
H: Performance of Buy & Hold portfolio
I: Performance of tactical trading portfolio

My main formula is in column “I”. I am trying to determine how the value of a portfolio will change if an investor sells his holdings when the change in price (column E) declines by a given percentage and buy back into the stock when it increases by a given percentage. Columns “F” & “G” make those determinations. For example, the investor would sell the stock if it declines by 4 percent, sit on cash (annual cash rate is in \$I\$9), and go back into the market when it increases by 2 percent. I think I’ve figured out how to sell and sit on cash, but I can’t get the formula to buy back again after a percentage increase.

Here are the formulas I’m using for each column:
C: no formula, just names of the month
D: random month-end prices
E: =D22/D21-1
F: =IF(E22<-0.04,"S","H")
G: =IF(\$E22>=0.02,"B","H")
H: =H21*(1+E22)
I: =IF(AND(F21="S",G22="H"),I21*(1+\$I\$9/12),IF(AND(F21="H",G22="H"),I21*(1+\$I\$9/12),IF(AND(F21="H",G22="B"),I21*(1+E22),I21*(1+E22))))

Thanks,

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### nbrcrunch

##### Well-known Member
not being in investment, many of us probably would have no clue whether or not your formula is good, bad or indifferent.

What are the underlying principles involved in this type of calculation? If you also are not sure, that would be the best place to start. Hopefully you have some resource, if not in the financial world, then maybe a school teacher specializing in financial analysis that can tell you what to consider. Thereafter, you can try formulating an English psuedo-math statement that can be converted to an Excel formula. It sounds like you need to first "wrap your head around" the concept and thereafter you should be home free. You may or may not even realize you're missing certain critical elements of the calculation--I really couldn't tell you.

#### PeetsDrinker

##### New Member
Thanks for your comments nbrcrunch. I guess I thought my question was pretty straightforward, but apparently it's not. I know exactly what I want to do, but am not explaining it clearly. I may have also given too much info and confused people.

#### nbrcrunch

##### Well-known Member
my apology. I misunderstood what you were saying. What is the problem with the formula you are using now? What isn't it doing?

#### PeetsDrinker

##### New Member
ok, I think I figured out what I’m having difficulty with and how to ask it in a non-financial way.
I’m working with these 4 cells:
A30 (value is either “Y” or “N”)
B30 (value is either “H” or “S”)
C30 (value is either “H” or “B”)
D30

I want to put a logical statement in D30 that states:
if B30 and C30 both = “H”, then D30 = A30
if B30 = “S” and C30 = “H”, then D30 = “N”
if B30 = “H” and C30 = “B”, then D30 = “Y”

Is it possible to put all these conditions into one cell??

Thanks

#### nbrcrunch

##### Well-known Member
=if(and(b30="H",c30="H"),a30,if(and(b30="S",c30="H"),"n",if(and(b30="H",c30="B"),"Y")))

Last edited:

#### PeetsDrinker

##### New Member
great, that worked. Thanks a lot.

Replies
1
Views
254
Replies
15
Views
408
Replies
4
Views
130
Replies
4
Views
242
Replies
4
Views
2K

1,191,225
Messages
5,985,366
Members
439,960
Latest member
Musa_dabban

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back