Why do you combine columns and Excel?

Posted on 2012-09-17
Last Modified: 2012-09-17
I am actually using the Google document, spreadsheet, but it seems to copy the Excel spreadsheet pretty closely. My question is:
why do you combine columns?

I have a pretty complicated function in a cell:
=round (((B12*vanguardInterestRate)/12), 0) & " (" & round (((B12 *vanguardInterestRate)/365.25), 2) & ")"

This just prints the monthly interest value of an account and the daily interest value of the account enclosed in parentheses. I would like the monthly interest value to have commas in it to make the amount more understandable. Instead of doing it the above way, should I break this into 2 columns for formatting purposes, and then combine them after I have formatted the cells? "VanguardInterestRate" is just a named cell to make the formula more understandable. "B12" is an amount of money.
Question by:dwcronin
    LVL 5

    Expert Comment

    I would suggest taking the 2 column approach. Use your formula in one column so, it give you the calculated interest. Use the second column and format it accordingly to display commas.

    If you want, after you are set with the second column, you can choose to hide the first one :)
    LVL 5

    Expert Comment

    Or simply use the 'Format Column' context menu option to choose the formatting required. So that way, you can have the formula work as is and the formatting applied will take care of the display.
    LVL 50

    Expert Comment


    If you want decimal points in the first part of the result, then don't use the Round() function

    =((B12*vanguardInterestRate)/12)&" ("&ROUND(((B12*vanguardInterestRate)/365.25),2)&")"

    If you want to break this into two cells, then use these two formulas, for example in

    A1  =((B12*vanguardInterestRate)/12)
    A2  =ROUND(((B12*vanguardInterestRate)/365.25),2)

    or, if you don't want to round the second part, use

    A2  =((B12*vanguardInterestRate)/365.25)

    You can then combine the two results as a text value with the & operator, using a formula like

    =A1&" ("&A2&")"

    Or, if you want to round the final results:

    =round(A1,2)&" ("&round(A2,2)&")"

    cheers, teylyn
    LVL 31

    Expert Comment

    by:Rob Henson
    WIth your formula in one cell, numeric formatting (commas, decimal places etc) won't work because the result of the formula is a text string.

    Therefore, if you want to keep it in one column you can enclose the formula within a TEXT function and force the format of the result.

    =TEXT(ROUND(((B12*vanguardInterestRate)/12), 0),"#,##0.0") & " (" & TEXT(ROUND (((B12 *vanguardInterestRate)/365.25), 2),"#,##0.0") & ")"

    As above will apply commas and 1 decimal place.

    Rob H
    LVL 31

    Accepted Solution

    Actually, taking another look, you don't need the ROUND section if you are going to force the format with the TEXT function; the result:

    =TEXT(((B12*vanguardInterestRate)/12),"#,##0.0") & " (" & TEXT(((B12 *vanguardInterestRate)/365.25),"#,##0.0") & ")"

    Rob H

    Author Comment

    Sameer_goyal:I think the entry is too complicated to use the format option. The line is like:
    12345 (405)
    I wanted to say:
    12,345 (405)
    Thank you, but I'm calculating future investment values like 40 years in the future-I don't think anything is exact enough to use lots of decimal points.
    Rob H: thank you! That's the type of answer that I was trying to get.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now