Counting addends

Topic

New Reply

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 non-VBA solution but would take a VBA solution if needed.

TIA

Fred

Reply | Quote

Replies

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 non-VBA solution.

–Scott.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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 1-digit 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hello – You can use ASAP Utilities (free for non-commercial use) at ….
http://www.asap-utilities.com/

There is a recent CNET review of ASAP Utilities at ……
http://download.cnet.com/ASAP-Utilities/3000-2077_4-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))

…

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

Has this been under my nose all this time!
Thanks Rory!

Reply | Quote

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

Reply | Quote

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 97-2003) 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.

Reply | Quote

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

Reply | Quote

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?

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.

Press f2 before using the arrow keys to toggle between cell selection and actually moving the cursor in the refersto box.

– where would I find info on GET.CELL?

Google macrofun.hlp and you should find hits. It’s the help file for the old XLM macro functions.
Edit: You can find it here.

Reply | Quote

Thanks Rory.

I knew about f2 for editing cell contents but never knew I could use that for typing in the Refers To box. That’s a time saver.

I’ll keep the NOW()*0 for now.

Fred

Reply | Quote