CODEWITHSUNDEEP
excelmathexcel-formulacell
Dan
Dan

Reputation: 2344

Extracting values in a single Excel cell for use in a forumla

I'm creating an Excel spreadsheet and I want to create a formula based on the contents of a cell. A basic example is below:

enter image description here

So, cells 1, 2 and 3 have values YxZ. I want to use the Y value and the Z value and do calculations based on each. I don't want to have to have separate cells for Y and Z.

So I'd want to single out the Y and do a simple addition to get 11. I'd then want to get the average of Z, which would be 225 in this case.

Can this be done? I'm by no means an Excel wizard!

Upvotes: 0

Views: 76

Answers (3)

John Coleman
John Coleman

Reputation: 51998

Here is a variation of @ScottCraner 's excellent answer:

Function Apply(Formulas As Range, operator As String, operand As Long, func As String) As Variant
    Dim terms As Variant
    Dim cell As Range
    Dim expression As String
    Dim i As Long

    ReDim terms(1 To Formulas.Cells.Count)
    i = 1

    For Each cell In Formulas.Cells
        terms(i) = Split(cell, operator)(operand - 1)
        i = i + 1
    Next cell

    expression = func & "(" & Join(terms, ",") & ")"
    Apply = Application.Evaluate(expression)

End Function

You pass a range of expressions of the form operand operator operand, a string representing the operator, and the integer 1 or 2 denoting either the operand before or the operand after the operator, as well as a string representing the function that you want to apply to the corresponding operands.

It works like this:

enter image description here

In the first column I use operand = 1 and in the second column I use operand = 2.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152465

A vba Option.

This User Defined Function will return the two numbers:

Function sumaver(rng As Range, dm As String)
Dim sm As Double
Dim avg As Double
Dim cnt As Long
Dim r As Range
Dim str() As String
Dim tmp(1 To 2)

For Each r In rng
    str = Split(r.Value, dm)
    If UBound(str) = 1 Then
        sm = sm + str(0)
        avg = avg + str(1)
        cnt = cnt + 1
    End If
Next r

tmp(1) = sm
tmp(2) = avg / cnt

sumaver = tmp

End Function

Put this in a module attached to the desired workbook. DO NOT put this in the worksheet or ThisWorkbook code.

Being that it returns an array of two numbers it can be called in two ways.

The first is to highlight two consecutive horizontal cell and in the first enter:

=sumaver(A2:H2,"x")

Then hit Ctrl-Shift-Enter instead of Enter. If done correctly Excel will automatically put {} around the formula and fill the second cell. The first will be the sum the second the average.

But if you wan the individual parts in different non contiguous horizontal cells then you can call the sum with this formula:

=INDEX(sumaver(A2:H2,"x"),1,1)

and the average with:

=INDEX(sumaver(A2:H2,"x"),1,2)

The parameters of the sumaver() function are sumaver(Range, character on which to split)

You can include empty cells in the range, they will be ignored.

enter image description here

Upvotes: 1

Aprillion
Aprillion

Reputation: 22324

you need array formulas to extract the text around x in each cell, convert to numbers and then sum the values together => confirm with Ctrl+Shift+Enter after copy&pasting to cells I2 (sum of Y) and J2 (sum-product of Z divided by sum of Y):

=SUM(IFERROR(VALUE(LEFT($A2:$H2,FIND("x",$A2:$H2)-1)),0))
=SUM(IFERROR(VALUE(LEFT($A2:$H2,FIND("x",$A2:$H2)-1)) * VALUE(MID($A2:$H2,FIND("x",$A2:$H2)+1,255)),0)) / $I2

Upvotes: 1

Related Questions