Why do you combine columns and Excel?

Posted on 2012-09-17
Medium Priority
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

Expert Comment

ID: 38404983
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 :)

Expert Comment

ID: 38404984
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
ID: 38404993

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 34

Expert Comment

by:Rob Henson
ID: 38405033
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 34

Accepted Solution

Rob Henson earned 2000 total points
ID: 38405123
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

ID: 38405270
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.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 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