divide a specified value by a specified number of months

[luis gaspper]

Hello everyone
I need some help with this file please
I have UDF Function to divide $ 15,000... will be divided by 36 months Beginning of the next month as follows :-
The first installment will be $ 475 ...The rest will be $ 415 to the end of the installments.
But I would like to specify a condition To get this discount Let it be ( OK )
Other than that $ 10,000 will be divided by 36 months Beginning of the next month as follows
The first installment will be $ 375 .. The rest will be $ 275 to the end of the installments.
I have highlighted the lines I want to add In the code editor
This is what I've come up with so far, Please see the attachment file ...Thanks in advance

[HansV]

Like this:

Code: Select all

Function SecondGroup(Condition As String, TheValue As Double, TheDate As Date) As Variant
    Dim Y As Long, M As Long, Yt As Long, Mt As Long
    Dim Nt As Long
    Dim c1 As Double
    Dim c2 As Double
    SecondGroup = CVErr(xlErrNA)
    Application.Volatile
    On Error GoTo NiceExit
    Y = Year(TheDate)
    M = Month(TheDate)
    Yt = Year(Now)
    Mt = Month(Now)
    Nt = Int(Now)
     
    If Nt >= DateSerial(Y, M + 1, 1) And Nt <= DateSerial(Y, M + 37, 0) Then
        Select Case Condition
            Case "OK"
                c1 = 0.9725
                c2 = 415
            Case Else
                c1 = 0.9625
                c2 = 275
        End Select
        If Mt - M = 1 Then
            SecondGroup = c2 * (TheValue * c1 / c2 - Int(TheValue * c1 / c2)) + TheValue * (1 - c1)
        Else
            SecondGroup = (TheValue - c2 * (TheValue * c1 / c2 - Int(TheValue * c1 / c2)) - TheValue * (1 - c1)) / 35
        End If
    Else
        SecondGroup = vbNullString
    End If
NiceExit:
End Function

Change the formula in G2 to

=SecondGroup(A2,B2,C2)

[luis gaspper]

That's great. Thank you very much Mr. Hans for your great help.
please, I would like to make one slight change If possible.
Column B contains the values to be divided ... How I can perform the entire calculation in the UDF Without adding this column.
Thanks in advance.

[HansV]

I don't understand, sorry.

[luis gaspper]

I mean to include the values to be divided in the code ... Thanks again.

[HansV]

That doesn't help, I still don't know what you want.

[luis gaspper]

The goal of my question is to include the values that will be divided In the code

[HansV]

That doesn't make sense to me.

[luis gaspper]

I don't think so
The goal is Determine a value $ 15,000 for the status (OK) as well as the value $ 10,000 from within the code
without adding a column for values.... Please see the following picture
I greatly appreciate your effort to help me ...Thanks again.

[HansV]

Code: Select all

Function SecondGroup(Condition As String, TheDate As Date) As Variant
    Dim Y As Long, M As Long, Yt As Long, Mt As Long
    Dim Nt As Long
    Dim c1 As Double
    Dim c2 As Double
    Dim TheValue As Double
    SecondGroup = CVErr(xlErrNA)
    Application.Volatile
    On Error GoTo NiceExit
    Y = Year(TheDate)
    M = Month(TheDate)
    Yt = Year(Now)
    Mt = Month(Now)
    Nt = Int(Now)
   
    If Nt >= DateSerial(Y, M + 1, 1) And Nt <= DateSerial(Y, M + 37, 0) Then
        Select Case Condition
            Case "OK"
                c1 = 0.9725
                c2 = 415
                TheValue = 15000
            Case Else
                c1 = 0.9625
                c2 = 275
                TheValue = 10000
        End Select
        If Mt - M = 1 Then
            SecondGroup = c2 * (TheValue * c1 / c2 - Int(TheValue * c1 / c2)) + TheValue * (1 - c1)
        Else
            SecondGroup = (TheValue - c2 * (TheValue * c1 / c2 - Int(TheValue * c1 / c2)) - TheValue * (1 - c1)) / 35
        End If
    Else
        SecondGroup = vbNullString
    End If
NiceExit:
End Function

Change the formula in C2 to =SecondGroup(A2,B2)

[luis gaspper]

Mr. Hans, thank you. Wow, your skills are awesome. and may be back for a question .... Again, thank you.

[luis gaspper]

Mr. Hans, I tried follow your approach to coding But I did not succeed in that.
Please, this is another UDF and it's the same idea
but $ 15,000 will be divided by 10 months from the beginning of the next month to the end of the installments equally.

Code: Select all

Function FirstGroup(TheValue As Double, TheDate As Date) As Variant
    Dim Y As Long, M As Long, Yt As Long, Mt As Long
    Dim Nt As Long
    Dim I As Double
    
    FirstGroup = CVErr(xlErrNA)
    Application.Volatile
    On Error GoTo NiceExit
    Y = Year(TheDate)
    M = Month(TheDate)
    Yt = Year(Now)
    Mt = Month(Now)
    Nt = Int(Now)
    
    If Nt >= DateSerial(Y, M + 1, 1) And Nt <= DateSerial(Y, M + 11, 0) Then
        FirstGroup = TheValue / 10
    Else
        FirstGroup = vbNullString
    End If
NiceExit:
End Function

How can to divide $ 15,000 by 10 months equally for case (OK) Other than that $ 10,000 will be divided by 10 months equally.
Thanks advanced for help

[HansV]

How about

Code: Select all

Function FirstGroup(Condition As String, TheDate As Date) As Variant
    Dim Y As Long, M As Long
    Dim Nt As Long
    FirstGroup = CVErr(xlErrNA)
    Application.Volatile
    On Error GoTo NiceExit
    Y = Year(TheDate)
    M = Month(TheDate)
    Nt = Date
    
    If Nt >= DateSerial(Y, M + 1, 1) And Nt <= DateSerial(Y, M + 11, 0) Then
        If Condition = "OK" Then
            FirstGroup = 15000 / 10
        Else
            FirstGroup = 10000 / 10
        End If
    Else
        FirstGroup = vbNullString
    End If
NiceExit:
End Function

The first argument must be the cell that contains OK (or not), the second argument must be the cell containing the date you want to use.

[luis gaspper]

Alright, thanks Hans ... Last question please
How I can give $ 1000 in a specific month for case (OK) Let it be January for example ... Otherwise, the result is a blank.
Thanks again.

[HansV]

Does this do what you want?

Code: Select all

Function FirstGroup(Condition As String, TheDate As Date) As Variant
    FirstGroup = CVErr(xlErrNA)
    Application.Volatile
    On Error GoTo NiceExit
    
    If Month(TheDate) = 1 And Condition = "OK" Then
        FirstGroup = 1000
    Else
        FirstGroup = vbNullString
    End If
NiceExit:
End Function

[luis gaspper]

Thanks Hans for great help ... Glad to find the editing in my codes....all codes is working perfect.
I greatly appreciate your effort to help me ... Have a nice time

[luis gaspper]

Sorry for disturbing you again Mr. Hans
What if I want to show the results from sheet to other
Please have a look at the example in "Outputs sheet" to see what I mean.
Many thanks for your time again.

[Oliver]

Luis,
1. Do you want that to show results with formulas or do you want that to happen in the background?
2. Where in the sheet do you want the results to go?

[HansV]

In A8 on the Outputs sheet:

=IF('Main File'!C2="","",'Main File'!C2)

Fill to the right, then down.

[luis gaspper]

This is known to me
I want show the results in the Outputs sheet as a values beginning from row 8
This may not be logically ... but I don't know if this is possible or not
Anyway, All Thanks! You are creative as usual and you have many solutions for any problem ..and thanks a lot M. Oliver for sharing me my issue.
Best Regards from the deep of my heart