# Posts by MickG

• ## calculate an annual 1% grows - how much grows is that for every one of the 12 months

Re: calculate an annual 1% grows - how much grows is that for every one of the 12 mon

This may help !!!
1000 @ 1% for a year =1000 +(1000*0.01) = 1000*(1+0.1)=1000*1.01 = 1010
If you just want to know how it will grow if compounded monthly to achieve the same "1010", you have to find the 12th root of 1.01 this equals 1.01^(1/12) =1.00082953811if you then compound your 1000 over 12 months, which equals 1010.
[TABLE="width: 268"]

[tr]

[td][/td]

[td]

Yearly Rate

[/td]

[td]

Mthly rate

[/td]

[/tr]

[tr]

[td][/td]

[TD="align: right"]1.01[/TD]
[TD="align: right"]1.000829538[/TD]

[/tr]

[tr]

[td]

Month

[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: right"]1[/TD]
[TD="align: right"]1000.830[/TD]

[td]

=1000*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]2[/TD]
[TD="align: right"]1001.660[/TD]

[td]

=C4*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]1002.491[/TD]

[td]

=C5*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]4[/TD]
[TD="align: right"]1003.322[/TD]

[td]

=C6*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]5[/TD]
[TD="align: right"]1004.155[/TD]

[td]

=C7*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]1004.988[/TD]

[td]

=C8*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]7[/TD]
[TD="align: right"]1005.821[/TD]

[td]

=C9*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]8[/TD]
[TD="align: right"]1006.656[/TD]

[td]

=C10*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]9[/TD]
[TD="align: right"]1007.491[/TD]

[td]

=C11*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]10[/TD]
[TD="align: right"]1008.326[/TD]

[td]

=C12*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]11[/TD]
[TD="align: right"]1009.163[/TD]

[td]

=C13*\$C\$2

[/td]

[/tr]

[tr]

[TD="align: right"]12[/TD]
[TD="align: right"]1010.000[/TD]

[td]

=C14*\$C\$2

[/td]

[/tr]

[/TABLE]

• ## Divide a number into two parts so that the resulting sum is equal to the input

Re: Divide a number into two parts so that the resulting sum is equal to the input

An alternative
Data Column "A" results "B"

• ## Check through Column A for matched data - copy text from column F

Re: Check through Column A for matched data - copy text from column F

Try this:-
Results start "I1"

• ## VBA Dictionary ... if word in Row 1 already exits then "word"&"1"

Re: VBA Dictionary ... if word in Row 1 already exits then &quot;word&quot;&amp;&quot;1&quot;

Thank for the feedback, Hope this helps !!

• ## VBA Dictionary ... if word in Row 1 already exits then "word"&"1"

Re: VBA Dictionary ... if word in Row 1 already exits then &quot;word&quot;&amp;&quot;1&quot;

Try:-

• ## Row Data Covert To Column date wise

Re: Row Data Covert To Column date wise

Try this:-
Data "Sheet2", Results sheet "Result"

• ## Display Name Range as Image based on Choice List Selection

Re: Display Name Range as Image based on Choice List Selection

You could write a bit of code around the code below to achieve what you want:-
NB:- Where range "A1" is perhaps a DropDown (Validation list) of named ranges

Code
``````Dim x As Object
ActiveWorkbook.Names(Range("A1").Value).RefersToRange.Copy
ActiveSheet.Range("M1").Select
• ## Search for array of substrings in a string

Re: Search for array of substrings in a string

Perhaps:-

Code
``=contains(A1,"hello,Great,Forum")``

Code
``````Function Contains(strBaseString As Range, strSearchTerm As String) As Boolean
'Purpose: Returns TRUE if one string exists within another
Dim W As Variant
For Each W In Split(strSearchTerm, ",")
Contains = InStr(strBaseString, W)
If Contains Then Exit Function
Next W
End Function``````
• ## calculate the interest rate.

Re: calculate the interest rate.

Not sure if you have what you want or Not. This may Help:-
[TABLE="width: 261"]

[tr]

[td]

P = present value =7770.59

[/td]

[/tr]

[tr]

[td]

F = Final value =10085.99

[/td]

[/tr]

[tr]

[td]

n = Number of years = 3

[/td]

[/tr]

[tr]

[td]

r = interest rate = "To Be found"

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

F = P*(1+r)^n

[/td]

[/tr]

[tr]

[td]

r=[(F/P)^(1/n)]-1

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

Find Rate = r

[/td]

[/tr]

[tr]

[td]

((10085.99/7770.59)^(1/3))-1 = r

[/td]

[/tr]

[tr]

[td]

r =0.0908244057013661

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

Check " r" to find Present Value = F

[/td]

[/tr]

[tr]

[td]

F=7770.59*(1+0.0908244057013661)^3

[/td]

[/tr]

[tr]

[td]

F=10085.99

[/td]

[/tr]

[/TABLE]

• ## calculate the interest rate.

Re: calculate the interest rate.

P = present value
F = Final value
n = Number of years
r = interest rate

F = P*(1+r)^n
r=[(F/P)^(1/n)]-1

• ## Extract unique values withing a range and the results would be in one cell

Re: Extract unique values withing a range and the results would be in one cell

You're welcome

• ## Extract unique values withing a range and the results would be in one cell

Re: Extract unique values withing a range and the results would be in one cell

Try this UDF :-
Run as:- =UnQ("select range require here !!!)

• ## Extract unique values withing a range and the results would be in one cell

Re: Extract unique values withing a range and the results would be in one cell

Try:-

• ## Short and split common multicolumn value

Re: Short and split common multicolumn value

You're welcome

• ## Short and split common multicolumn value

Re: Short and split common multicolumn value

Try this:-
Nb:- Data from sheet "Master", results in sheet "Results" start "A4".
This code and its results are based on the results in sheet "After".( Latest )

• ## Short and split common multicolumn value

Re: Short and split common multicolumn value

Please supply a more comprehensive "After" sheet. Your requirements are not too clear from the limited data shown.

• ## Short and split common multicolumn value

Re: Short and split common multicolumn value

Try this:-
NB:- Your data is assumed to start in "A1" Reference the header word "Date".
The results start Sheet2 "A1".

• ## sorting continuos blocks of data in column A

Re: sorting continuos blocks of data in column A

You're welcome

• ## sorting continuos blocks of data in column A

Re: sorting continuos blocks of data in column A

Try this:-

Code
``````Sub aSort
Dim rng As Range, Dn As Range
Set rng = Range("A:A").SpecialCells(xlCellTypeConstants, 1)
For Each Dn In rng.Areas
Dn.Sort Dn(1), xlAscending
Next Dn
End Sub``````