"TREND" condition

[sal21]

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

[HansV]

I'm sorry, I don't understand. What are the 'given' data, and what do you want to compute?

[sal21]

I'm sorry, I don't understand. What are the 'given' data, and what do you want to compute?

the sheet contain value based day, week, month and year for giune and july 2014.
I need to calculate the trend year based the two month and retirn the related value for day, week, month, year...

[HansV]

Please explain clearly and explicitly what you want. I have no idea.

[HansV]

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.

[sal21]

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.

Attached new file.
I need to calculate the trend for August 2014, is the formula in column G, correct?

[HansV]

Yes, that looks OK.

[sal21]

Yes, that looks OK.

ok tath is the first good new!

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.

[HansV]

I'd rearrange the data.

[sal21]

I'd rearrange the data.

BIG progress!

Why have different result from the formula and the code?

See the code in module and formula in G2.

[HansV]

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

[sal21]

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

WOW! TKS

[sal21]

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

Hans sorry me but is possible to use a Cells reference to set the range?

[HansV]

What do you mean?

[sal21]

What do you mean?

Set knowny = .Range("D2:F2")
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))
?

[HansV]

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)

[sal21]

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

WHY?????????????

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(?!)

[HansV]

Try this 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

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

[sal21]

Try this 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

Always same error 1004....

=======================================================

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

[HansV]

It works for me - see

TEST_TREND.xls