Results 1 to 15 of 16
Thread: Counting addends

20091216, 23:40 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Hi Loungers,
I'm looking for a way to count the addends in a formula. For example, I might have the formula
= 5+2+3
in a cell and I'd like to know that there are 3 items being added. More addends can be put at the end of the formula as time goes by.
The initial state of the cell would be empty. But when I add the first item, it might be done with a formula like =4 or just with a number 4. If the latter, then when I add another addend, I'd have to change that. For either case, the result should be 1.
I would prefer a nonVBA solution but would take a VBA solution if needed.
TIA
Fred

20091216, 23:57 #2
 Join Date
 Dec 2009
 Location
 Central Texas, USA
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Count the number of plus signs and then add 1?
Example: addend = len(A1)len(replace(A1,"+","")+1
This replaces the plus signs with nothing, and the difference in length is the number of plus signs.
If you have the "=" at the front, I doubt that there's a nonVBA solution.
Scott.

20091217, 03:28 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Instead of modifying the formula each time, enter the numbers 5, 2, 3 etc. in cells, let's say A1, A2, A3, ...
In another column, let's say in C1, enter the formula =SUM(A:A) to get the sum of the numbers, and in C2, enter the formula =COUNT(A:A) to get the count.

20091218, 20:28 #4
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Thanks Hans,
I didn't say enough to justify doing it the way I requested.
The application is a gradebook. So each row represents a different student. In, say, col Q, I have a formula like =2+3+1 to indicate extra credit earned by that student. So the number of points earned is 6.
In another part of the file (on another sheet) I just developed a need to count how many extra credit assignments the student did, hence the need to count addends.
Doing it the way you suggested would certainly alter the logic of the file. What I could do is create a new sheet for extra credit assignments with rows still corresponding to individual students and then implement your approach of 1 cell per assignment. The number of assignments in a semester is probably no more than 5 or 6. Then col Q in the existing spreadsheet would get the sume of that student's row in the new sheet and the other sheet would get the count. This might have some other advantages too that probably aren't needed for discussion.
However, I'd like to see if there are any other approaches to using what I already have.
PS: I haven't been on the Lounge in a long time. Am I missing the way to reply to a particular post, like yours vs Scott's? Or is the philosophy that I just reply to the thread?

20091218, 20:35 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Thanks Scott.
I tried your approach. Doesn't look like it would work. For one thing, the formula's give a number, so len of =2+3+2 is 1 (7 is a 1digit number) and there are no + signs to replace. I had thought of SUBSTITUTE before I posted my original request; not sure that REPLACE works but SUBSTITUTE definitely does NOT work because of the above. What I was also looking for was some way of converting the formula to text using some function and then using something like what you said but could not find any way to do that either.
Fred

20091219, 02:35 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Fred, this lightly tested User Defined Function (UDF) should do what you want  as written it assumes that only plus and minus signs are used in the formula:
Public Function GetAddends(rngSourceCell As Range) As Long
' returns count of addends where rngSourceCell is the cell containing the formula to be checked
Dim strF As String
Dim lngC As Long
Application.Volatile True
GetAddends = 1 ' for the starting '='
strF = rngSourceCell.Formula
For lngC = 1 To Len(strF)
If Mid(strF, lngC, 1) = "+" Then GetAddends = GetAddends + 1
If Mid(strF, lngC, 1) = "" Then GetAddends = GetAddends + 1
Next lngC
End Function
UDFs can be placed in a standard VBA module in the workbook they are used in and entered directly into a cell, or if saved in Personal.xls, accessed through the formula wizard.John ... I float in liquid gardens
UTC 7ąDS

20091219, 16:55 #7
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Further to John's reply, here is a similar UDF that does not use a loop
Code:Function Appends(f As Range) Dim intPlus As Integer Dim intMinus As Integer If f.HasFormula Then intPlus = Len(f.Formula)  Len(Replace(f.Formula, "+", "")) intMinus = Len(f.Formula)  Len(Replace(f.Formula, "", "")) Else End If Appends = intPlus + intMinus + 1 End Function
Jerry

20091220, 14:06 #8
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hello  You can use ASAP Utilities (free for noncommercial use) at ....
http://www.asaputilities.com/
There is a recent CNET review of ASAP Utilities at ......
http://download.cnet.com/ASAPUtilit...10781628.html
As long as each number in your formula is less than 10 you can use the following ...
Assume your formula of = 5+2+3 is in Cell A1
To get the formula use this in B1 ....
=ASAPGETFORMULA(A1)
To get the number of items use this in C1....
=LEN(ASAPEXTRACTNUMBERS(B1))
...

20091220, 16:47 #9
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Its an interesting question  at least for me. I frequently want to examine the contents of a formula.
Anyhow  making a twist on Scotts' suggestion.
You will need intermediate cells for this.
Copy and paste your extra credit formulas into a new column.
Leave them highlighted.
Use the Find/Replace function on the toolbar to replace the = at the beginning of the formula with a blank or '.
This turns the formulas into a string. You can now use the formula that Scott suggested.
(I'm using substitute).
=LEN(B1)LEN(SUBSTITUTE(B1,"+",""))
Drawback, its not automatically updating. You'll need to copy/paste/replace as the source data updates.[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20091222, 04:25 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
If you define a name (called say CountOperands) as:
=LEN(GET.CELL(6,INDIRECT("RC[1]",FALSE)))LEN(SUBSTITUTE(GET.CELL(6,INDIRECT("RC[1]",FALSE)),"+",""))+1+NOW()*0
it will count the number of pluses in the cell one to the left of where you use it and add one.
I leave it to you to apply it to your layout...Regards,
Rory
Microsoft MVP  Excel

20091222, 15:25 #11
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Has this been under my nose all this time!
Thanks Rory![b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20091224, 20:09 #12
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Thanks to all that replied. Some great ideas. Unfortunately my DSL modem went dead a few days ago and I just got my new one working.
Rory  I was thinking there might be something with CELL but couldn't recall where I'd seen the documentation for this. Excel 2003 doesn't have a problem with CELL (it's even in HELP) but doesn't know what a first of arg of 6 means (gives #VALUE); all 1st args are words like "contents" or "row". GET.CELL results in a msg box with "That function is not valid". As far as adjusting the use of this to my layout, that wouldn't be trivial either but I'll wait to see if I can just get this to work for 1 cell to the left.
I may very well go the way that Hans hinted at (create a new sheet for the extra credit and do a count) or use one of the UDF's.
Fred

20091225, 03:32 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
You can't use GET.CELL in a cell formula. You have to use it in a defined name, as Rory explicitly stated.
Select Insert  Name  Define... (in Excel 972003) or activate the Formula tab of the ribbon, then select Define Name  Define Name in the Defined Names group (Excel 2007 and presumably 2010).
Type CountOperands in the name box.
Type
=LEN(GET.CELL(6,INDIRECT("RC[1]",FALSE)))LEN(SUBSTITUTE(GET.CELL(6,INDIRECT("RC[1]",FALSE)),"+",""))+1+NOW()*0
in the Refers To box.
Click OK.
You can now use the formula =CountOperands in the cell to the right of a cell with a formula such as =2+3+5.

The Following User Says Thank You to HansV For This Useful Post:
JohnS0603 (20150815)

20100103, 18:31 #14
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Hi All,
Thks for the clarification on the use of the GET.CELL.
A few things:
 why is +NOW()*0 added at the end of the formula? I understand something like this is sometimes done to convert the previous expression into a numeric value. But isn't the LEN(...)LEN(...) already a numeric entry?
 In general, I'm having a little trouble entering the formula into the Refers To box. Given this is such a long formula, I needed to edit it to make it fit into my structure (which includes using CountOperand to count the + signs in a formula on another sheet  boy was this a pain but I finally did get it). I tried using right/left arrow while editing the Refers To box but that just enters the cell reference of my current cell. I delete this and that moves the formula over so I can edit it further. But this is a pain. I also tried to paste the correct formula into the Refers To box (having entered it w/o an = in another cell, but this does not work well, if at all. I did eventually get the formula entered and adjusted to my workbook structure.
 where would I find info on GET.CELL?
Thanks all.
Fred

20100104, 08:37 #15
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
It makes the formula (extremely) volatile. In reality, you probably don't need it here due to the use of INDIRECT anyway.
 In general, I'm having a little trouble entering the formula into the Refers To box. Given this is such a long formula, I needed to edit it to make it fit into my structure (which includes using CountOperand to count the + signs in a formula on another sheet  boy was this a pain but I finally did get it). I tried using right/left arrow while editing the Refers To box but that just enters the cell reference of my current cell. I delete this and that moves the formula over so I can edit it further. But this is a pain. I also tried to paste the correct formula into the Refers To box (having entered it w/o an = in another cell, but this does not work well, if at all. I did eventually get the formula entered and adjusted to my workbook structure.
 where would I find info on GET.CELL?
Edit: You can find it here.Regards,
Rory
Microsoft MVP  Excel