Addition formula. - Page 2

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

Thread: Addition formula.

  1. #11
    Board Regular
    Join Date
    Oct 2017
    Location
    Tasmania
    Posts
    132
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for help with an addition formula.

     
    Ah interesting. Also don't be sorry, if I haven't given you something that works, that's not your fault!

    Hmm I'm a little stumped as to how you could do this neatly in one formula. I'll have to give it some thought

  2. #12
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    5,633
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for help with an addition formula.

    This kind of iterative processing is very tricky to accomplish with native Excel functions, especially with non-continuous functions. Compu-babble aside, it just means that the result of the February calculation is dependent on the result of the January calculation, etc. In order to accomplish this, you actually need to calculate each individual monthly total, then select the one you need. For example:

    Excel 2012
    AF AG AH AI AJ AK AL AM AN AO AP AQ AR
    4 8-Dec
    5 45 45 45 45 45 45 45 45 45 45 45 45 45
    Sheet1

    Worksheet Formulas
    Cell Formula
    AF5 =IF(MONTH($AF$4)=1,$D5,OFFSET($AG5,0,MONTH($AF$4)-1))
    AG5 =MIN(60,MAX(0,IF(COLUMN($AG5)=COLUMN(AG5),$D5,AF5)+1-SUM(OFFSET($F5:$G5,0,(COLUMNS($AG5:AG5)-1)*2))))



    Put the formula in AG5, and drag it across to AR5. Then put in the AF5 formula. You can now hide columns AG:AR if you want.


    This is a bit easier with VBA. If you want to try that, open a COPY of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. In the window that opens, paste this code:
    Code:
    Public Function GetLeave(ByVal StartVal As Long, ByVal MonthVals As Range, MyDate As Date) As Long
    Dim i As Long
        
        For i = 1 To Evaluate("MONTH(""" & MyDate & """)")
            StartVal = Evaluate("MAX(0,MIN(60," & StartVal - MonthVals(1, 2 * i - 2) - MonthVals(1, 2 * i - 1) + 1 & "))")
        Next i
        GetLeave = StartVal
    End Function
    Press Alt-Q to exit the editor. Now in AF5, put this formula:

    =GetLeave(D5,F5:AC5,$AF$4)

    The first parameter is the starting value, the next is the range containing the monthly totals, and the last is the date. With the appropriate use of $, you can drag this formula down the column just like any other formula.

    Hope this helps.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #13
    New Member
    Join Date
    Oct 2017
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for help with an addition formula.

    This is a great reply!! Thank you. Although, I literally just 5 minutes ago managed to do this by adding a column after every month. I went a completely different approach with it, since I kept getting stuck.


    So in the very first "Days available" I ended up using =IF(OR(F5="",)*OR(G5=""),"",MIN(60,MAX(0,SUM(-F5,-G5,D5+1))))

    THEN... in the following "Days available" (February) I take =IF(OR(I5="",)*OR(J5=""),"",MIN(60,MAX(0,SUM(-I5,-J5,H5+1))))

    So basically the first month takes beginning balance, and the following months take from the previous month. Just like you said

    It's not exactly what I was originally looking for, but we're happy with the results. It gives me a good running tally, and at the end of the year I sum it all up. So it's good now.



    Anyways, just wanted to say thanks for the help. It was definitely a good learning experience. Thank you both for your help and replies!!!!!!!
    Last edited by figuare9; Dec 8th, 2017 at 10:15 AM.

  4. #14
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    5,633
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for help with an addition formula.

    My first stab at this actually involved adding another column for each month, like you ended up doing. I didn't know if you wanted to do that, which is why I added them all on the right. Incidentally, you can shorten your first formula a bit:

    =IF(COUNTA(F5:G5)=0,"",MEDIAN(0,60,D5-F5-G5+1))

    and the other one in the same way. MEDIAN returns the middle number of the 3, and if you look at all the possibilities, you'll see it works out right in each case.

    In any case, glad we could help, and glad you got something that works for you!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #15
    Board Regular
    Join Date
    Oct 2017
    Location
    Tasmania
    Posts
    132
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for help with an addition formula.

      
    Nice figuare9. This is also where I was heading... an additional column was the only way I could get it to work without VBA, for the exact iteration issue that Eric W stated. Glad we could have some fun with it along the way though!

User Tag List

Tags for this Thread

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
  •  

 

 
DMCA.com