Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Help with finance formula

  1. #11
    New Member
    Join Date
    Sep 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with finance formula

    Quote Originally Posted by dirkgeraedts View Post
    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. #12
    Board Regular
    Join Date
    Mar 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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