the nearest value from the average

[malek]

Hello everyone,

I would like from a selection of a range, color the nearest value from the average of that range. (ONLY WITH A VBA CODE)

I don't know if it's clear but here is my VBA code (it's not working of course), and below the excel file sheet Exercice3 to understand better my demand.

So I want when I select a random area in sheet exercice 3 to color the cell that have the nearest value from the average.

Thank you by advance.

 

[Marc L]

Hello, an Excel basics starter VBA demonstration :​

Sub Demo1()
    With Selection
            V = Evaluate(Replace("IF({1},ABS(#-AVERAGE(#)))", "#", .Address))
            M = Application.Min(V)
        For R = 1 To .Rows.Count
        For C = 1 To .Columns.Count
            If V(R, C) = M Then .Item(R, C).Interior.Color = vbYellow: Exit Sub
        Next C, R
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[malek]

Thank you!

Could you please just explain the line with evaluate=.... I don't understand many things in this line for example : # {1}... And what do they stand for.

 

[Marc L]

# is the character to be replaced. This line evaluates each value minus the average, {1} forces the calculation as a formula array …​

​

 

[malek]

it's working. is that another way to do it without using replace and evaluate ?

 

[Marc L]

Yes with some dumb loop instead of a clever efficient Excel formula …​

​

 

[malek]

I tried to do that but no way. Any help?

 

[Marc L]

As I already gave you an Excel efficient way so I won't code any worse !​

​

First do it manually by 'hands' with a pencil and a paper obviously on a small range​

in order to find out the appropriate logic which should be closed to my post #2 demonstration.​

Then you should easily write your new VBA procedure from blank according to your 'paper logic'​

as you can trash your orginal code with several logic errors …​

​

 

[Marc L]

My demonstration revamped for a more efficient Excel basics way without any useless loop :​

Sub Demo1r()
         A = Application.Average(Selection)
         D = Evaluate("MIN(ABS(" & Selection.Address & "-" & Replace(A, ",", ".") & "))")
    Set Rf = Selection.Find(A - D)
     If Rf Is Nothing Then Set Rf = Selection.Find(A + D)
        Rf.Interior.Color = vbYellow
    Set Rf = Nothing
End Sub

You may Like it !​

​

 

[malek]

Thank you!!