Count input and twisted sum

S

samitnair

Board Regular
Joined
Jul 5, 2010
Messages
155
Hi

I don't think if am asking a appropriate question or not but still i'll get a clear picture...

If i have a expression like 10+200+750+220 i need a formula which will do the following

1. Count how many items are being added also if any of the item value exceeds 250 consider it as 2
Result: Generally it is 4, but i want the formula to consider it as 6 (10+200+250+250+250+220)

2. the expression which i mentioned above is text value how can i make it a formula... adding "=" in front of every column is tough. So can anyone create a vb if possible.


Regards
Samit
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
You can do this with native formulas:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">10</td><td style=";">Count:</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">750</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">220</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">{=SUM(<font color="Blue">ROUNDUP(<font color="Red">A1:A4/250,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Upvote 0
Hi,

Maybe a UDF....
Code: 
Option Explicit

Function TextToNumber(ByVal TextString As String) As Double
Dim iElementPtr As Integer, iTextPtr As Integer
Dim dblResult As Double, dblCurValue As Double
Dim saElements() As String, sCurElement As String, sCurChar As String, sCurOperator As String
Dim sTextString As String

sTextString = TextString & "+"
dblResult = 0

iElementPtr = -1
sCurElement = ""
For iTextPtr = 1 To Len(sTextString)
    sCurChar = Mid$(TextString & "+", iTextPtr, 1)
    Select Case sCurChar
    Case "+", "-", "*", "/"
        dblCurValue = Val(sCurElement)
        Select Case sCurOperator
        Case "-"
            dblResult = dblResult - dblCurValue
        Case "/"
            dblResult = dblResult / dblCurValue
        Case "*"
            dblResult = dblResult * dblCurValue
        Case Else
            dblResult = dblResult + dblCurValue
        End Select
        dblCurValue = 0
        sCurElement = ""
        sCurOperator = sCurChar
    Case Else
        sCurElement = sCurElement & sCurChar
    End Select
Next iTextPtr
TextToNumber = dblResult
End Function

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Text Sring</td><td style="font-weight: bold;text-align: center;;"># Elements</td><td style="font-weight: bold;text-align: center;;">Value</td><td style="font-weight: bold;text-align: center;;">Formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">10+200+750+220</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1180</td><td style="text-align: center;;">1180</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">10+200+750+220-333</td><td style="text-align: center;;">5</td><td style="text-align: center;;">847</td><td style="text-align: center;;">847</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">10+200+750+220*10</td><td style="text-align: center;;">5</td><td style="text-align: center;;">11800</td><td style="text-align: center;;">11800</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">10+200+750+220/10</td><td style="text-align: center;;">5</td><td style="text-align: center;;">118</td><td style="text-align: center;;">118</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=LEN(<font color="Blue">A2</font>)-LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">A2,"*","+"</font>),"/","+"</font>),"-","+"</font>),"+",""</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=texttonumber(<font color="Blue">A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=10+200+750+220</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=LEN(<font color="Blue">A3</font>)-LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">A3,"*","+"</font>),"/","+"</font>),"-","+"</font>),"+",""</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=texttonumber(<font color="Blue">A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=10+200+750+220-333</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=LEN(<font color="Blue">A4</font>)-LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">A4,"*","+"</font>),"/","+"</font>),"-","+"</font>),"+",""</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=texttonumber(<font color="Blue">A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=(<font color="Blue">10+200+750+220</font>)*10</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=LEN(<font color="Blue">A5</font>)-LEN(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">A5,"*","+"</font>),"/","+"</font>),"-","+"</font>),"+",""</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=texttonumber(<font color="Blue">A5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=(<font color="Blue">10+200+750+220</font>)/10</td></tr></tbody></table></td></tr></table><br />

note that the UDF performs arithmetic strictly sequentially
 
Upvote 0
Hi thanks Mr. Kowz and Mr. al_b_cnu

Mr. Kowz my knowledge in excel if very low and i couldnt understand the way the formula is working..

Mr. al_b_cnu thanks for the udf it works perfectly fine to find how many items are being added and converting text to number...


samitnair said:
Hi

1. Count how many items (10+200+750+220) are being added also if any of the item value exceeds 250 consider it as follows
Result: Generally it is 4, but i want the formula to consider it as 6 (10+200+250+250+250+220)
Click to expand...

but my question is only partially resolved...is there a way by which i can achieve the next half..


Thanks regards
 
Upvote 0
Hi, sorry, I missed that requirement :oops:

Try this:
Code: 
Option Explicit

Function TextToNumber(ByVal TextString As String) As Double
Dim iElementPtr As Integer, iTextPtr As Integer
Dim dblResult As Double, dblCurValue As Double
Dim saElements() As String, sCurElement As String, sCurChar As String, sCurOperator As String
Dim sTextString As String

sTextString = TextString & "+"
dblResult = 0

iElementPtr = -1
sCurElement = ""
For iTextPtr = 1 To Len(sTextString)
    sCurChar = Mid$(TextString & "+", iTextPtr, 1)
    Select Case sCurChar
    Case "+", "-", "*", "/"
        dblCurValue = Val(sCurElement)
        Select Case sCurOperator
        Case "-"
            dblResult = dblResult - dblCurValue
        Case "/"
            dblResult = dblResult / dblCurValue
        Case "*"
            dblResult = dblResult * dblCurValue
        Case Else
            dblResult = dblResult + dblCurValue
        End Select
        dblCurValue = 0
        sCurElement = ""
        sCurOperator = sCurChar
    Case Else
        sCurElement = sCurElement & sCurChar
    End Select
Next iTextPtr
TextToNumber = dblResult
End Function

Function CountElements(ByVal TextString As String, Optional NumberDivisor As Integer = 250) As Integer
Const sOperators As String = "-*/"

Dim iPtr As Integer, iCount As Integer
Dim sTextString As String, saElements() As String

sTextString = TextString
'-- Replace all Aritmetic operators with a +
For iPtr = 1 To Len(sOperators)
    sTextString = Replace(sTextString, Mid$(sOperators, iPtr, 1), "+")
Next iPtr
iCount = 0
If sTextString <> "" Then
    saElements = Split(sTextString, "+")
    For iPtr = 0 To UBound(saElements)
        iCount = iCount + WorksheetFunction.Ceiling(Val(saElements(iPtr)), NumberDivisor) / NumberDivisor
    Next iPtr
End If
CountElements = iCount
End Function

Which yields these results:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Text Sring</td><td style="font-weight: bold;text-align: center;;"># Elements</td><td style="font-weight: bold;text-align: center;;">Value</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">10+200+750+220</td><td style="text-align: center;;">6</td><td style="text-align: center;;">1180</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">10+200+750+220-333</td><td style="text-align: center;;">8</td><td style="text-align: center;;">847</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">10+200+750+220*10</td><td style="text-align: center;;">7</td><td style="text-align: center;;">11800</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">10+200+750+220/10</td><td style="text-align: center;;">7</td><td style="text-align: center;;">118</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=countelements(<font color="Blue">A2,250</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=texttonumber(<font color="Blue">A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=countelements(<font color="Blue">A3,250</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=texttonumber(<font color="Blue">A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=countelements(<font color="Blue">A4,250</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=texttonumber(<font color="Blue">A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=countelements(<font color="Blue">A5,250</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=texttonumber(<font color="Blue">A5</font>)</td></tr></tbody></table></td></tr></table><br />

note the addition of the UDF "CountElements"
 
Upvote 0
Hi

I tried the new code and entered the formula

A2 (10+200+750+220)
B2 (=countelements(A2,250))

If i paste the formula it stays like a text and not formula or if i type the formula it gives me an error "#Name?"

I am using office 2010 is that the problem!!!
 
Upvote 0
Hi Samit,

The code I supplied additionally includes the UDF 'CountElements'. Copy the entire code above, switch to Excel VB window, [Ctrl-A] to select all the existing code & paste in the new code & overwrite the existing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Inventory disposal plan
Replies
3
Views
298
Spaceballs_the_fanboy
S
S
  • Question Question
Calculate product percentage impact on total percentage
Replies
1
Views
276
etaf
etaf
J
  • Solved
Calculate sums based on values
Replies
3
Views
297
NateSC
NateSC
T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
318
Tarkemelion
T
T
  • Solved
Integer programming with binary variables in Solver - issue with constraint?
Replies
3
Views
374
StephenCrump
S

Forum statistics

Threads
1,224,517
Messages
6,179,233
Members
452,898
Latest member
Capolavoro009

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top