Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    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 non-VBA solution but would take a VBA solution if needed.

    TIA

    Fred

  2. #2
    New Lounger
    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 non-VBA solution.

    --Scott.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    Silver Lounger
    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?

  5. #5
    Silver Lounger
    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 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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

  7. #7
    Platinum Lounger
    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

  8. #8
    5 Star Lounger
    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 non-commercial use) at ....
    http://www.asap-utilities.com/

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


    ...

  9. #9
    Super Moderator WebGenii's Avatar
    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

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  11. #11
    Super Moderator WebGenii's Avatar
    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

  12. #12
    Silver Lounger
    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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.

  14. The Following User Says Thank You to HansV For This Useful Post:

    JohnS0603 (2015-08-15)

  15. #14
    Silver Lounger
    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

  16. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by fburg View Post
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •