"TREND" condition

[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

PROJECT 90%....
now i need to rappresent the graph to consider the August month as a red trend line...?
how to?

last result in attached file.

[HansV]

The data are of very different magnitudes so it's not immediately clear how you want to chart them.
Please explain in more detail what the chart should look like, or draw an example.

[sal21]

It works for me - see

TEST_TREND.xls

Resolved my self!
The prob for error 1004 is only to set the correct format number in cells where are the value for calculatiion of Trend!!!!!!!!!!!!!!!!!!

[sal21]

It works for me - see

TEST_TREND.xls

I Hans instead to insert a graph i think:
Is the trend value in column G in increase, if yes insert the Wingdings3 character "Ç", else "È"... in the related cell of column H (named x factor)
I hope you understand me

Attached new workbook.

Note:
In column h i have insert the 2 caharcter only for test but not for rreal condition i mensioned in my question.

[HansV]

Does the attached version do what you want? I have changed the values to illustrate the effect.

TEST_TREND.xls

[sal21]

Does the attached version do what you want? I have changed the values to illustrate the effect.

TEST_TREND.xls

wow! Sure you are the "big"

If i have undestrand the Orange color is for a stable value, or not?
and instead one single orange arraw is possible to have 2 orange arrows with left orienation and right orientation? Wat is the inverse caharacter of "Æ"?

[HansV]

The leftward arrow is "Å"

[sal21]

Does the attached version do what you want? I have changed the values to illustrate the effect.

TEST_TREND.xls

Possible to trend arrow in column M, based the cod of agengy, month and total.
The trend is May vs June vs July


Note:
all agency have only 3 dates

[HansV]

See the attached version. I used formulas in column M, and conditional formatting.

Attachment removed at the request of Sal21. See next my reply for the formula.

[sal21]

See the attached version. I used formulas in column M, and conditional formatting.

Cartel3.xls

ok!!!!!!!!!!!!!!!!!!!!!!!!!WOW

But i dont see the arrow based Jule.

note:
the new file contain the correct and official ordering base agency and Date

[HansV]

Sorry, I didn't look carefully. Change the formula in M2 to

=IF(A2<>A3,"",IF(D2>D3,"Ç",IF(D2<D3,"È","ÅÆ")))

then fill down to the end.

[sal21]

Sorry, I didn't look carefully. Change the formula in M2 to

=IF(A2<>A3,"",IF(D2>D3,"Ç",IF(D2<D3,"È","ÅÆ")))

then fill down to the end.

Sorry bt now i can't test i can tomorow.

[sal21]

Sorry, I didn't look carefully. Change the formula in M2 to

=IF(A2<>A3,"",IF(D2>D3,"Ç",IF(D2<D3,"È","ÅÆ")))

then fill down to the end.

Is this correct if i insert the formula in code?:

Sub TEST_TREND_1()

With Sheets("REPORT_GENERALE").Range("M2:M2500")
.Formula = "=IF(A2<>A3,"",IF(D2>D3,'Ç',IF(D2<D3,'È','ÅÆ')))"
'.Value = .Value
'.NumberFormat = "#,##0.00"
End With

End Sub

[HansV]

You have to use double double quotes:

Code: Select all

        .Formula = "=IF(A2<>A3,"""",IF(D2>D3,""Ç"",IF(D2<D3,""È"",""ÅÆ"")))"