Correct Formula for This Combination

[Jasa P]

Good morning,

I'm trying to get a solution for my problem. I'm sure this is fairly simple, but I don't know how to resolve this combination. So, I need your help.

I have a combination of
20.10.10x5.15.25/
in cell A1

The problem are:
a. The result for combination before "x" should be the amount of dots (.) + 1, so the result is 3
b. The result for combination between "x" and "/" should be the sum of the numbers, so the result is 45

Then, the results from a and b must be multiplied, so the final result is 135 in cell B1

I'm using Excel 2007 and Windows XP

I'm sorry for my poor English.
Any help would be great. Thank you.

Best Regards

Jasa

 

[Jasa P]

You're welcome. Thanks for the feedback!

(Y)

 

[Jasa P]

The formula worked for me:

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:136px;"><col style="width:72px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">String</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Result</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">20.10.10x5.15.25/</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">135</td></tr></tbody></table>


Did you enter the formula as an array formula?


As far as using a VBA solution, if after a period of time has passed and no one has replied with a VBA solution then you should start a new thread. A reasonable period of time would be about 1 day.

Hi, sorry for disturbing u again. But, can I use this formula for more complicated combination? For example:

20.10.10x5.15.25/20.10.10x5.15/20x5.15.25/20.10x5.15/20.10x5.15.25/
The result should be 370 (45*3+20*3+45*1+20*2+45*2)

10x5.55.15/10.50x5.55.15/10.50.50x5.15/50x5.55/10.50x5.55.15/10.50x5.55/
The result should be 615 (75*1+75*2+20*3+60*1+75*2+60*2)

'Cause I've tried use the array formula, but the result just take the first combination before "/"

My goal are:
1. The result for combination before "x" should be the amount of dots (.) +1
2. The result for combination between "x" and "/" should be the sum of the numbers
3. The final result should be the multiplied of 1 and 2

Can you help me to resolve this one? thank you so much. I put the image too, thank u

http://www.flickr.com/photos/77270117@N02/6780010088/

Any help would be great
and I really appreciate your help, thank u so much

Best Regards

Jasa

 

[T. Valko]

Hi, sorry for disturbing u again. But, can I use this formula for more complicated combination? For example:

20.10.10x5.15.25/20.10.10x5.15/20x5.15.25/20.10x5.15/20.10x5.15.25/
The result should be 370 (45*3+20*3+45*1+20*2+45*2)

10x5.55.15/10.50x5.55.15/10.50.50x5.15/50x5.55/10.50x5.55.15/10.50x5.55/
The result should be 615 (75*1+75*2+20*3+60*1+75*2+60*2)

'Cause I've tried use the array formula, but the result just take the first combination before "/"

My goal are:
1. The result for combination before "x" should be the amount of dots (.) +1
2. The result for combination between "x" and "/" should be the sum of the numbers
3. The final result should be the multiplied of 1 and 2

Can you help me to resolve this one? thank you so much. I put the image too, thank u

http://www.flickr.com/photos/77270117@N02/6780010088/

Any help would be great
and I really appreciate your help, thank u so much

Best Regards

Jasa

Yow!

I don't think that would be possible using a single formula.

If you can split the entry at each slash into individual cells then you could use the orginal formula. You can use Text to Columns to split the data into indivdual cells.

 

[chirp]

vba solution:

note this is not blazingly fast, and there are probably better ways to do this. with more than one entry using split might be more efficient.

enter into a standard code module and then you can use it from the worksheet like:

=funnySum(a1)

Function funnySum(vStr As String) As Long
Dim bArr() As Byte, conCnt As Long, stArr() As String
Dim dotCnt As Long, isLeft As Boolean, i As Long, j As Long
Dim tmpSum As Long

If vStr = vbNullString Then Exit Function
stArr = Split(vStr, "/")

For j = LBound(stArr) To UBound(stArr)
    bArr = stArr(j)
    For i = UBound(bArr) - 1 To 0 Step -2
        If isLeft Then
            If bArr(i) < 47 Then dotCnt = dotCnt + 1
        Else
            If bArr(i) > 47 Then
                If bArr(i) < 58 Then
                    tmpSum = tmpSum + (bArr(i) - 48) * 10 ^ conCnt
                    conCnt = conCnt + 1
                ElseIf bArr(i) = 120 Then
                    isLeft = True
                End If
            Else
                conCnt = 0
            End If
        End If
    Next
    funnySum = funnySum + tmpSum * (dotCnt + 1)
    tmpSum = 0: dotCnt = 0
    conCnt = 0: isLeft = False
Next

End Function

 

[Jasa P]

Yow!

I don't think that would be possible using a single formula.

If you can split the entry at each slash into individual cells then you could use the orginal formula. You can use Text to Columns to split the data into indivdual cells.

Hi, you're right. it can be done. But, it will require a lot of time to separate them because the data contained in considerable amounts. But, thank you so much for helping me. I really appreciate that. Thank u so much, thank u It useful,

 

[T. Valko]

Hi, you're right. it can be done. But, it will require a lot of time to separate them because the data contained in considerable amounts. But, thank you so much for helping me. I really appreciate that. Thank u so much, thank u It useful,

Good luck!

 

[Jasa P]

vba solution:

note this is not blazingly fast, and there are probably better ways to do this. with more than one entry using split might be more efficient.

enter into a standard code module and then you can use it from the worksheet like:

=funnySum(a1)

Function funnySum(vStr As String) As Long
Dim bArr() As Byte, conCnt As Long, stArr() As String
Dim dotCnt As Long, isLeft As Boolean, i As Long, j As Long
Dim tmpSum As Long

If vStr = vbNullString Then Exit Function
stArr = Split(vStr, "/")

For j = LBound(stArr) To UBound(stArr)
    bArr = stArr(j)
    For i = UBound(bArr) - 1 To 0 Step -2
        If isLeft Then
            If bArr(i) < 47 Then dotCnt = dotCnt + 1
        Else
            If bArr(i) > 47 Then
                If bArr(i) < 58 Then
                    tmpSum = tmpSum + (bArr(i) - 48) * 10 ^ conCnt
                    conCnt = conCnt + 1
                ElseIf bArr(i) = 120 Then
                    isLeft = True
                End If
            Else
                conCnt = 0
            End If
        End If
    Next
    funnySum = funnySum + tmpSum * (dotCnt + 1)
    tmpSum = 0: dotCnt = 0
    conCnt = 0: isLeft = False
Next

End Function

Hi , now I understand what you mean. By adding code to a standard code module and paste the formula

= funnySum (A1)

, my problem has been resolved. I am grateful for your help, It's very useful to me. Awesome! I really appreciate your help, thank you so much for helping me. thank u thank u so much

 

[Jasa P]

Good luck!

Thanks again...

Best regards,

Jasa

 

[Jasa P]

vba solution:

note this is not blazingly fast, and there are probably better ways to do this. with more than one entry using split might be more efficient.

enter into a standard code module and then you can use it from the worksheet like:

=funnySum(a1)

Function funnySum(vStr As String) As Long
Dim bArr() As Byte, conCnt As Long, stArr() As String
Dim dotCnt As Long, isLeft As Boolean, i As Long, j As Long
Dim tmpSum As Long

If vStr = vbNullString Then Exit Function
stArr = Split(vStr, "/")

For j = LBound(stArr) To UBound(stArr)
    bArr = stArr(j)
    For i = UBound(bArr) - 1 To 0 Step -2
        If isLeft Then
            If bArr(i) < 47 Then dotCnt = dotCnt + 1
        Else
            If bArr(i) > 47 Then
                If bArr(i) < 58 Then
                    tmpSum = tmpSum + (bArr(i) - 48) * 10 ^ conCnt
                    conCnt = conCnt + 1
                ElseIf bArr(i) = 120 Then
                    isLeft = True
                End If
            Else
                conCnt = 0
            End If
        End If
    Next
    funnySum = funnySum + tmpSum * (dotCnt + 1)
    tmpSum = 0: dotCnt = 0
    conCnt = 0: isLeft = False
Next

End Function

Sorry, but there's a little problem here.

When I saved the workbook, there's command that asked me to save in Macro-enabled file type. Then, I re-opened it and security warning came out, asked me to enable macro. It has been done. But, when I tried to take a result for another columns and rows, #NAME? was appeared.

Why is it happening? Do u have any solution for this one?

And after I paste the code in standard code module, should I just close them then continue to the workbook, or should I save it first before continue to workbook? Thank u so much

Any help would be great

Best regards,

Jasa

 

[chirp]

if you have saved it in a macro enabled workbook (.xlsm) then you should be able to use it.

the steps i would take are:

1: insert the standard code module which you seem to have done.
2: save as an xlsm
3: when reopening click "enable macros"

this should work, and i do not know why it would not, especially if you are being prompted to enable macros.

is the code saved in the workbook that you are using?