1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Excel - calculate cells with numbers and text

Discussion in 'Business Applications' started by bbnan, Apr 23, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. bbnan

    bbnan Thread Starter

    Joined:
    Apr 18, 2019
    Messages:
    3
    So my office has some reports we do regularly which have a six column list of figures that need totalling. As the reports are amended, the #s can change, so they asked if I could set up an embedded excel table to help make the process a lot faster and less error-prone. So far so good. The problem is that sometimes those numbers have to be marked as "tentative" and I'm not sure how to achieve this, or if I even can.

    - I can't add a column because it can appear in any cell and my 6 column table would turn into a 12 column table and be very very tiny on the word page.

    - There's too great a diversity in computer knowledge to just teach people "insert a row and add the word there and then remove the border between the row above it to make it all look like one cell" - plus of course this kind of kills the time savings again!

    - I found this set of instructions, but whether in an excel table (which I'd prefer for the can't-mess-it-up "total" row) or just plain cells, I can't make it work - either it ignores the number altogether and the total stays $0.00 or I get a "#VALUE!" error.

    I've attached a sample file with attempts I've made - please be gentle, all my excel knowledge is self-learned over decades of just "I need to do a thing, let's figure out how to do it using a combination of google, trying stuff until it works, and as a last resort, begging for help from people who actually know what they're doing, and then if it still confuses me, trying to reverse-engineer it in case I need to do it again in the future!" :)
     

    Attached Files:

  2. lochlomonder

    lochlomonder

    Joined:
    Jul 24, 2015
    Messages:
    2,591
    Why is it saved as a macro-enabled spreadsheet?
     
  3. bbnan

    bbnan Thread Starter

    Joined:
    Apr 18, 2019
    Messages:
    3
    Because otherwise it won't save once I add the VBA code in the link I referenced above.
     
  4. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    378
    First Name:
    Jack
    Hello bbnan,

    The problem with your attempt above is that SumNumbers will only sum a single cell at a time (at least to my knowledge), so attempting to apply this function to a range =SumNumbers(B2:B5) will always fail. The SumNumbers function will work with individually added cells though; such as:

    =SUMNUMBERS(B2)+SUMNUMBERS(B3)+SUMNUMBERS(B4)+SUMNUMBERS(B4); because, it is using the Add function. HOWEVER, you will be required to drop the leading dollar sign for cells that contain text, or the value will not be correctly read by Excel.

    An alternate way to accomplish this would be to use SumProduct and Substitute out constant text.

    ASSUMING that there will always be a space between your dollar amount and the word "tentative", and assuming that "tentative is always spelled correctly, this formula should move you in the right direction (place it in your B6 cell).

    =SUMPRODUCT(--SUBSTITUTE(Table1[Limits of Coverage]," tentative","")) Please note that all of the cells to be computed must have a value (even if it is $0.00) for this formula to work.

    And another alternate, PROBABLY Better solution (unless "tentative" is being used for other calculations) would be to add small Check Box columns after each column to indicate that the value has a temporary status (label the columns as " T "). Then you could use a simple Sum function to calculate the column totals; because, you would no longer have text co-mingled with your numerical values. ;) Of course, you would need to check for impact to other customized reporting features that your company may already have in use.

    Good Luck!
     
    bbnan likes this.
  5. bbnan

    bbnan Thread Starter

    Joined:
    Apr 18, 2019
    Messages:
    3
    Oh lord! You're right, I went back and looked and I read that section wrong - and the image demonstrates exactly what it SHOULD do, I just didn't look close enough at the picture but focused on the text and the code! Thank you for that - and THANK YOU SO MUCH for your additional suggestions! These give me several things to try and in fact I think your final solution just might be the best, but I'm going to play with the others just to see what I can learn from it. Just shows what a good idea it is, after focusing hard on a project for too long, it is to talk it over w/others just in case you're missing something blindingly obvious!! :LOL:
     
  6. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    378
    First Name:
    Jack
    We have all been there! I think I was yesterday. :D

    Always glad to lend a hand.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1226175

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice