CODEWITHSUNDEEP
excelvbaexcel-formula
Dr.PB
Dr.PB

Reputation: 1077

max from single cell, and then mean

      A
1    5,6
2    8,0

What I want:

Adding vba:

Sub maxAndMean()
    TotalNonZeroC = 0
    sumOfMax = 0
    For Each c In Selection
        If IsEmpty(c.Value) = False Then
            TotalC = TotalC + 1
            maxVal = "=Max(Left(c.Value, 1), Right(c.Value, 1))"
            'Not working formula^
            If maxVal != 0 Then
                sumOfMax = sumOfMax + maxVal
                TotalNonZeroC = TotalNonZeroC + 1
            End If
        End If
    Next c

    avg = sumOfMax / TotalNonZeroC
    'How to write avg to next cell(row) in the selected column?
End Sub

Upvotes: 0

Views: 100

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37460

With VBA it's pretty simple:

Sub ExtractAverage()
    a1max = MaxFromArray(Split(Cells(1, 1), ","))
    a2max = MaxFromArray(Split(Cells(2, 1), ","))
    Cells(3, 1).Value = (a1max + a2max) / 2
End Sub
' note that you can easily find more optimal ways to find maximum of an array,
' This isn't the most optimal solution.
Function MaxFromArray(arr As Variant) As Long
    element = arr(LBound(arr))
    For Each e In arr
        If e > element Then
            element = e
        End If
    Next
    MaxFromArray = element
End Function

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

To get the MAX with a formula, if you have Excel 2013+

=MAX(FILTERXML("<t><s>" & SUBSTITUTE(A2,",","</s><s>") & "</s></t>","//s"))

For the AVERAGE, just average the column of MAX's. And note that AVERAGE will ignore blank cells

EDIT

Another variation, where the AVERAGE formula only needs to refer to the original data:

MAX for each cell

=FILTERXML("<t><s>" & SUBSTITUTE(A2,",","</s><s>") & "</s></t>","//s[not(. < preceding::*) and not(. < following::*)]")

AVERAGE for the Range

=AVERAGE(FILTERXML("<t><s>" & SUBSTITUTE(A2:A3,",","</s><s>") & "</s></t>","//s[not(. < preceding::*) and not(. < following::*)]"))

The AVERAGE formula is an array formula. In some versions of Excel, to enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

enter image description here

If you have 2007-2010, you can use, for Max:

=AGGREGATE(14,6,--TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),seq_99,99)),1)

where

seq_99 refers to:  =IF(ROW($A$1:INDEX($A:$A,255,1))=1,1,(ROW($A$1:INDEX($A:$A,255,1))-1)*99)

Upvotes: 2

Daghan
Daghan

Reputation: 769

This works for your example but will not scale like a good VBA solution.

A3=your nice formula:

=AVERAGE(MAX(LEFT(A1;1);RIGHT(A1;1));MAX(LEFT(A2;1);RIGHT(A2;1)))

Upvotes: -1

Related Questions