"TREND" condition
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
"TREND" condition
I have 4 value, for day, week, month and year based the month 6, and 4 value, for day, week, month and year based the month 7 (same year 2014).
i need to calculate the "calculation of the annual tendency" from month 7 and the compared month 6.
Sorry for my bad english.
But the Professor Hans understnd me, i'm sure:-)
for example:
LUGLIO/2014 GIUGNO/2014
MEDIA GIORN. RA(day) 191.980,11 232.104,94
MEDIA SETT.LE (week) 959.900,55 1.160.524,70
MEDIA MENS.LE(month) 4.165.968,39 5.036.677,20
MEDIA ANN.LE(year) 50.068.412,69 60.532.968,35
i need to calculate the "calculation of the annual tendency" from month 7 and the compared month 6.
Sorry for my bad english.
But the Professor Hans understnd me, i'm sure:-)
for example:
LUGLIO/2014 GIUGNO/2014
MEDIA GIORN. RA(day) 191.980,11 232.104,94
MEDIA SETT.LE (week) 959.900,55 1.160.524,70
MEDIA MENS.LE(month) 4.165.968,39 5.036.677,20
MEDIA ANN.LE(year) 50.068.412,69 60.532.968,35
Last edited by sal21 on 25 Jul 2014, 11:02, edited 1 time in total.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Spectacular" condition
I'm sorry, I don't understand. What are the 'given' data, and what do you want to compute?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "Spectacular" condition
the sheet contain value based day, week, month and year for giune and july 2014.HansV wrote:I'm sorry, I don't understand. What are the 'given' data, and what do you want to compute?
I need to calculate the trend year based the two month and retirn the related value for day, week, month, year...
Last edited by sal21 on 25 Jul 2014, 10:40, edited 1 time in total.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Spectacular" condition
Please explain clearly and explicitly what you want. I have no idea.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Spectacular" condition
If it's too difficult for you to explain the problem in English, you may be better off asking your question in an Italian-language forum.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "Spectacular" condition
Attached new file.HansV wrote:If it's too difficult for you to explain the problem in English, you may be better off asking your question in an Italian-language forum.
I need to calculate the trend for August 2014, is the formula in column G, correct?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "TREND" condition
ok tath is the first good new!HansV wrote:Yes, that looks OK.
prob!!!!! the original file not have the cells in contiguous range, and the month are reverse order, from july > may and not to may>july!
see the new file.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "TREND" condition
BIG progress!HansV wrote:I'd rearrange the data.
Why have different result from the formula and the code?
See the code in module and formula in G2.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "TREND" condition
That is because the known x-es and the new x are not 1, 2, 3, and 4, but 1 May 2014, 1 June 2014, 1 July 2014, and 1 August 2014. This version produces the same result as the formula:
Code: Select all
Sub TEST_TREND()
Dim knowny As Range
Dim knownx As Range
Dim newx As Range
Dim myval As Double
Dim WS As Worksheet
Set WS = Sheets("FOGLIO1")
With WS
Set knowny = .Range("D2:F2")
Set knownx = .Range("D1:F1")
Set newx = Range("G1")
myval = Application.WorksheetFunction.Trend(knowny, knownx, newx)
End With
Debug.Print Format(myval(1), "#,##0.00")
End Sub
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "TREND" condition
WOW! TKSHansV wrote:That is because the known x-es and the new x are not 1, 2, 3, and 4, but 1 May 2014, 1 June 2014, 1 July 2014, and 1 August 2014. This version produces the same result as the formula:
Code: Select all
Sub TEST_TREND() Dim knowny As Range Dim knownx As Range Dim newx As Range Dim myval As Double Dim WS As Worksheet Set WS = Sheets("FOGLIO1") With WS Set knowny = .Range("D2:F2") Set knownx = .Range("D1:F1") Set newx = Range("G1") myval = Application.WorksheetFunction.Trend(knowny, knownx, newx) End With Debug.Print Format(myval(1), "#,##0.00") End Sub
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "TREND" condition
Hans sorry me but is possible to use a Cells reference to set the range?HansV wrote:That is because the known x-es and the new x are not 1, 2, 3, and 4, but 1 May 2014, 1 June 2014, 1 July 2014, and 1 August 2014. This version produces the same result as the formula:
Code: Select all
Sub TEST_TREND() Dim knowny As Range Dim knownx As Range Dim newx As Range Dim myval As Double Dim WS As Worksheet Set WS = Sheets("FOGLIO1") With WS Set knowny = .Range("D2:F2") Set knownx = .Range("D1:F1") Set newx = Range("G1") myval = Application.WorksheetFunction.Trend(knowny, knownx, newx) End With Debug.Print Format(myval(1), "#,##0.00") End Sub
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "TREND" condition
Set knowny = .Range("D2:F2")HansV wrote:What do you mean?
Set knownx = .Range("D1:F1")
for example:
set this range by cells and not .Range("D2:F2")
is this correct:
Set knownx = .Range(Cells(2, 4), Cells(2, 6))
?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "TREND" condition
Almost. You have to use .Cells, just like you use .Range:
Code: Select all
Set knowny = .Range(.Cells(2, 4), .Cells(2, 6))
Set knownx = .Range(.Cells(1, 4), .Cells(1, 6))
Set newx = .Cells(1, 7)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "TREND" condition
WHY?????????????HansV wrote:That is because the known x-es and the new x are not 1, 2, 3, and 4, but 1 May 2014, 1 June 2014, 1 July 2014, and 1 August 2014. This version produces the same result as the formula:
Code: Select all
Sub TEST_TREND() Dim knowny As Range Dim knownx As Range Dim newx As Range Dim myval As Double Dim WS As Worksheet Set WS = Sheets("FOGLIO1") With WS Set knowny = .Range("D2:F2") Set knownx = .Range("D1:F1") Set newx = Range("G1") myval = Application.WorksheetFunction.Trend(knowny, knownx, newx) End With Debug.Print Format(myval(1), "#,##0.00") End Sub
error in image when the codce go in:
myval = Application.WorksheetFunction.Trend(knowny, knownx, newx)
in effect i need to loop all 6 lines and insert in column G the related Trend value
note:
in other case i have changed the var Dim myval() As Variant, in the first you code you have set Double(?!)
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "TREND" condition
Try this version:
A shorter version:
Code: Select all
Sub TEST_TREND()
Dim knowny As Range
Dim knownx As Range
Dim newx As Range
Dim myval As Variant
Dim WS As Worksheet
Dim I As Long
Set WS = Sheets("FOGLIO1")
With WS
For I = 2 To 6
Set knowny = .Range("D" & I & ":F" & I)
Set knownx = .Range("D1:F1")
Set newx = .Range("G1")
myval = Application.WorksheetFunction.Trend(knowny, knownx, newx)
.Range("G" & I).Value = Round(myval(1), 2)
.Range("G" & I).NumberFormat = "#,##0.00"
Next I
End With
End Sub
Code: Select all
Sub TEST_TREND()
With Sheets("FOGLIO1").Range("G2:G6")
.Formula = "=ROUND(TREND(D2:F2,D$1:F$1,G$1),2)"
.Value = .Value
.NumberFormat = "#,##0.00"
End With
End Sub
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: "TREND" condition
HansV wrote:Try this version:
Always same error 1004....Code: Select all
Sub TEST_TREND() Dim knowny As Range Dim knownx As Range Dim newx As Range Dim myval As Variant Dim WS As Worksheet Dim I As Long Set WS = Sheets("FOGLIO1") With WS For I = 2 To 6 Set knowny = .Range("D" & I & ":F" & I) Set knownx = .Range("D1:F1") Set newx = .Range("G1") myval = Application.WorksheetFunction.Trend(knowny, knownx, newx) .Range("G" & I).Value = Round(myval(1), 2) .Range("G" & I).NumberFormat = "#,##0.00" Next I End With End Sub
=======================================================
with short version instead, see iamge...
A shorter version:
Code: Select all
Sub TEST_TREND() With Sheets("FOGLIO1").Range("G2:G6") .Formula = "=ROUND(TREND(D2:F2,D$1:F$1,G$1),2)" .Value = .Value .NumberFormat = "#,##0.00" End With End Sub
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "TREND" condition
It works for me - see
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans