divide a specified value by a specified number of months
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
divide a specified value by a specified number of months
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
Like this:
Change the formula in G2 to
=SecondGroup(A2,B2,C2)
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
=SecondGroup(A2,B2,C2)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
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.
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.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
I don't understand, sorry.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
I mean to include the values to be divided in the code ... Thanks again.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
That doesn't help, I still don't know what you want.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
The goal of my question is to include the values that will be divided In the code
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
That doesn't make sense to me.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
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.
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.
You do not have the required permissions to view the files attached to this post.
Last edited by luis gaspper on 21 Jan 2021, 15:47, edited 1 time in total.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
Mr. Hans, thank you. Wow, your skills are awesome. and may be back for a question .... Again, thank you.
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
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.
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
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
Thanks advanced for help
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
How about
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.
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
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.
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.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
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
I greatly appreciate your effort to help me ... Have a nice time
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- 2StarLounger
- Posts: 128
- Joined: 08 Mar 2010, 13:53
- Location: Missouri
Re: divide a specified value by a specified number of months
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?
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?
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: divide a specified value by a specified number of months
In A8 on the Outputs sheet:
=IF('Main File'!C2="","",'Main File'!C2)
Fill to the right, then down.
=IF('Main File'!C2="","",'Main File'!C2)
Fill to the right, then down.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 68
- Joined: 03 Aug 2020, 05:23
Re: divide a specified value by a specified number of months
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
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