Thanks:  0
Likes:  0

# Thread: Help with finance formula

1. ## Re: Help with finance formula

Originally Posted by dirkgeraedts
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.

2. ## Re: Help with finance formula

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•