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

 

[Tubal]

Mole,

Can you provide some typical values for I6:Q6 ? (the possible cells used by the formula)
like I6: currency; J6: text; L6: equation with #n/a or a number...

I expect it can be done. I also expect it may be slower (but a lot cleaner to look at!)

Tubal

 

[mole999]

I (actually it is J now) which derives an average of the last three cells from =IFERROR(AGGREGATE(1,3,OFFSET(K7,0,(COUNTA(L7:N7)-COUNTIF(L7:N7,"#N/A")),1,-3)),"")

K to N are purely numbers percentage or decimal

the idea being that I drive an arrow (wingdings) from a change of the last complete column against the previous column (without throwing errors when a column doesn't have a value)

will need to tweak a couple of varients (about 4) so its getting an understanding of how to construct such a beast

 

[mole999]

currently a bit brain dead and not seeing this

Function neutral(rangea As Range, rangeb As Range)
IF(OFFSET(Rangea,0,((COUNTA(Rangeb)-COUNTIF(Rangeb,"#N/A")-1)),1,1) = OFFSET(Rangea,0,(COUNTA(Rangeb)-COUNTIF(Rangeb,"#N/A")),1,1))
then "¢"
End If
IF(OFFSET(Rangea,0,(COUNTA(Rangeb)-COUNTIF(Rangeb,"#N/A")),1,1) >= OFFSET(Rangea,0,((COUNTA(Rangeb)-COUNTIF(Rangeb,"#N/A")-1)),1,1)
then "é"
else "ê"
End If
End Function

won't compile and I can't figure out what is the issue. i'm doing something fundamently wrong and can't see where

 

[jasonb75]

Mole,

See if this is of any use, the 2 specialcells counts at the top of the code are not functioning correctly, the first should count the errors, the second should count text and numbers (formulas, not constants), but for some reason both are counting all cells regardless of value.

I've used application.volatile in conjunction with Sub_Calc to enable testing, if you're not familiar with this, you can't normally step through a function for testing, but by making it volatile, then stepping through the sub to recalculate, you can.

Option Explicit
Function mole999(rng As Range) As String
Application.Volatile
Dim valcount As Long, errcount As Long, tmp As String
errcount = rng.SpecialCells(xlFormulas, 16).Count
valcount = rng.SpecialCells(xlFormulas, 3).Count

If rng(valcount - errcount) = rng(valcount - errcount + 1) Then
    tmp = "¢"
ElseIf rng((valcount - errcount + 1)) >= rng(valcount - errcount) Then
    tmp = "é"
Else: tmp = "ê"
End If
mole999 = tmp

End Function
Sub calc()
Application.Calculate
End Sub

Hopefully this will get you going in the right direction, I'll keep looking to see if I can find a fix.

 

[mole999]

if i could ensure I always have a value in every column I would say we were close, the #N/A was allowing the code to disregard last column. i will have to read up on that special cells

would having two named ranges work better, rather than checking rng twice for two differnt things


grasping at straws

 

[jasonb75]

You would still have to do a check on the second range so one would effectively counter the other.

I tried looping through the cells in rng but that didn't work either.

Could you give a little more detail on what you have in your sheet and what you're trying to achieve. I based the code on your formula, not the expected outcome because there is not enough detail to visualise what you're working with.

I won't be back on til midnight or so, if I think of anything else before then i'll post it when I get back.

 

[mole999]

concept
To check across a row, identify the last cell with a valid value. then use the proceeding three cells to establish an average of the last three VALID cells which drives the wingding arrows

[TABLE="width: 495"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Last
Period
Change[/TD]
[TD] [/TD]
[TD]Av.[/TD]
[TD]Period
1[/TD]
[TD]Period
2[/TD]
[TD]Period
3[/TD]
[TD]Period
4
[/TD]
[/TR]
[TR]
[TD]é[/TD]
[TD] [/TD]
[TD]124317.3[/TD]
[TD="align: right"]118141[/TD]
[TD="align: right"]132366[/TD]
[TD="align: right"]118463[/TD]
[TD="align: right"]122123[/TD]
[/TR]
[TR]
[TD]ê[/TD]
[TD] [/TD]
[TD]4052.7[/TD]
[TD="align: right"]4219[/TD]
[TD="align: right"]4270[/TD]
[TD="align: right"]3949[/TD]
[TD="align: right"]3939[/TD]
[/TR]
[TR]
[TD]é[/TD]
[TD] [/TD]
[TD]200.0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]é[/TD]
[TD] [/TD]
[TD]8.2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD] #N/A

[/TD]
[/TR]
</tbody>[/TABLE]

 

[jasonb75]

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

 

[Rick Rothstein]

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