Advice Please

[mole999]

Can this
=IFERROR(IF(OFFSET(I6,0,((COUNTA(J6:M6)-COUNTIF(J6:M6,"#N/A")-1)),1,1)=OFFSET(I6,0,(COUNTA(J6:M6)-COUNTIF(J6:M6,"#N/A")),1,1),"¢",IF(OFFSET(I6,0,(COUNTA(J6:M6)-COUNTIF(J6:M6,"#N/A")),1,1)>=OFFSET(I6,0,((COUNTA(J6:M6)-COUNTIF(J6:M6,"#N/A")-1)),1,1),"é","ê")),"")

be turned into a

FUNCTION

advice welcome

I have no idea where to start

 

[mole999]

I believe the following UDF (user defined function) will work for you... simply pass it the range for your Periods on the row you are examining. So, for the first row of date (Row 4), this would be your formula...

=ArrowCode(D4:G4)

then copy the formula down to the last data row. Here is the code for the UDF...

Function ArrowCode(Rng As Range) As String
  Dim LastNumCol As Long, Avg As Double
  LastNumCol = Evaluate(Replace("MAX(IF(ISNUMBER(@),COLUMN(@)))", "@", Rng.Address))
  Avg = Application.Average(Range(Cells(Rng(1).Row, Application.Max( _
        LastNumCol - 2, Rng(1).Column)), Cells(Rng.Row, LastNumCol)))
  ArrowCode = Choose(Sgn(Cells(Rng.Row, LastNumCol).Value - Avg) + 2, "ê", "¢", "é")
End Function

Rick, certainly compact I'm doing a side by side compare and i have a few niggles (16 out of over 250 and is the same issue)

118141,132366,118463,122123 if I average those I get 124317, this code for arrow should produce an UP as 122123 is greater than 118463, i believe the code is saying the last cell is below the average of cells and represents as DOWN

The logic is
given four columns of data
the last column will be measured against the previous one, UP DOWN and the same will be modeled
IF only three columns of data exist, then the last full column is measured against the previous column
if the last column contains #N/A at the end then this is ignored and previous valid values in the last two columns are compared

my long formula fudge does that, but is unwieldy to manage

ultimately i will have two functions, one which will throw an UP arrow for where it is a numerical improvement, i will also have another one that throws the opposing arrow (i.e numbers decreasing is an improvement Waste is down which is an improvement)

stepping through after triggering appeared to show the function halting if i hit an #N/A ( i won't swear to that) could be the way i was testing

Thank you for your time on this

 

[mole999]

Mole, see if this works with real data.

Option Explicit
Function mole999(rng As Range) As String
Application.Volatile
Dim valcount As Long, errcount As Long, tmp As String, c As Range
For Each c In rng
    If IsError(c) Then
        errcount = errcount + 1
    ElseIf IsNumeric(c) Then
        valcount = valcount + 1
    End If
Next c
If rng(valcount - errcount - 1) = rng(valcount - errcount) Then
    tmp = "¢"
ElseIf rng((valcount - errcount)) >= rng(valcount - errcount - 1) Then
    tmp = "é"
Else: tmp = "ê"
End If
    mole999 = tmp
End Function
Sub calc()
Application.Calculate
End Sub

Jason
works well but falls over on a very few occassions, when the last column contains #N/A get the inverse of what I expected (logic on my reply to Rick)

 

[Rick Rothstein]

Rick, certainly compact I'm doing a side by side compare and i have a few niggles (16 out of over 250 and is the same issue)

118141,132366,118463,122123 if I average those I get 124317, this code for arrow should produce an UP as 122123 is greater than 118463, i believe the code is saying the last cell is below the average of cells and represents as DOWN

The logic is
given four columns of data
the last column will be measured against the previous one, UP DOWN and the same will be modeled
IF only three columns of data exist, then the last full column is measured against the previous column
if the last column contains #N/A at the end then this is ignored and previous valid values in the last two columns are compared

I think I am misunderstanding what you are doing. I thought you wanted the last number to be measured against the average. If all you want to do is measure it against the previous number, then I would think this code should do that for you...

Function ArrowCode(Rng As Range) As String
  Dim LastNumCol As Long, Avg As Double
  LastNumCol = Evaluate(Replace("MAX(IF(ISNUMBER(@),COLUMN(@)))", "@", Rng.Address))
  ArrowCode = Choose(Sgn(Cells(Rng.Row, LastNumCol).Value - Cells( _
              Rng.Row, LastNumCol - 1).Value) + 2, "ê", "¢", "é")
End Function

 

[mole999]

Thanks Rick, that appears to be exactly what I have been trying to achieve

very much obliged