Help with finance formula

[ryan james hardy]

Hi
I have made a table which i use to calculate potential gains made trading stock if gains are realised every time the stock gains. Hopefully the screenshot demonstrates what im trying to explain. Currently i am manually adding the formulas for each day. The formula changes depending on weather the gains have been on consecutive days or not (the brokerage cost changes). Anyone have any idea how i might go about using a conditional formula to calculate Columns B and D.

Comment	Start amount	Formula in B	End amount	Formula in C	action	day	date	Open	Adj Close*	ILC (ASX20)
	30000		29997.35166	"=(B4+(B4*(N4/100)))-(19.95*2)"		Tuesday	21-Jan-14			0.124172185
						Wednesday	22-Jan-14			-0.289375775
						Thursday	23-Jan-14			-1.034340091
						Friday	24-Jan-14			-0.166666667
	29997.35166	"=D4"	30092.77609	"=(B8+(B8*(N8/100)))-(19.95)"		Tuesday	28-Jan-14			0.384615385
	30092.77609	"=D8"	30181.921	"=(B9+(B9*(N9/100)))"		Wednesday	29-Jan-14			0.296233601
	30181.921	"=D9"	30290.40471	"=(B10+(B10*(N10/100)))-(19.95)"		Thursday	30-Jan-14			0.425531915
						Friday	31-Jan-14			-0.96598068
						Monday	3-Feb-14			-0.844238075
						Tuesday	4-Feb-14			-0.98206661
						Wednesday	5-Feb-14			-1.411462789
	30290.40471	"=D10"	30446.51682	"=(C15+(C15*(O15/100)))-(19.95*2)"		Thursday	6-Feb-14			0.647109577
						Friday	7-Feb-14			-0.042607584
						Monday	10-Feb-14			-0.504413619

<tbody>
</tbody>

Thanks in advance.

Ryan

 

[ryan james hardy]

I hope I understood your case correctly but the condition is as follow:

- If the previous day was positive then your end amount changed the previous day because you had a gain and otherwise your end amount remained unchanged because nothing happened.

In formula for cell B4: IF(N3>0,C3,B3)

Put is formula in every cell of column B and you will have the entire B column occupied with numbers. Which brings me back to your previous formula, if you put B4 before the final bracket of your formula in column C you will have the entire column C occupied with numbers as well. Start and End coluymn won't always show a difference (only on positive gain days) but this way you have some thing that looks more like a dataset. Another benefit you'll have from this structure is that you can easily add a delta column which is B4-C4. Just a thought

]

I found this formula will work to get the starting figure for each day

=IF(O5>=0,LOOKUP(2,1/(D$3:D4<>0),D$3:D4),0)

where O is ILC. Ideally though where it says <>0, it should be 0 or blank. I'm not sure how to incorporate the blank atm.

 

[dirkgeraedts]

The lookup formula has vectors as input variables and in vectors and arrays you cannot use the operators NOT/OR/AND. see also Daily Dose of Excel » Blog Archive » Logical operations in array formulas
With this in mind your formula is going to look something like this =IF(O5>=0,LOOKUP(2,1/(((D$3:D10=0) + ISBLANK(D$3:D10))>0),D$3:D10),0). Maybe this is exactly what you want but considering that it behaves a little different than the formula in your last post i think it isn't. However with the provided background information you will be able to adjust to your needs.