Why do you combine columns and Excel?

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.
dwcroninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
sameer_goyalCommented:
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 :)
0
 
sameer_goyalCommented:
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.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Rob HensonFinance AnalystCommented:
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.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
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") & ")"

Thanks
Rob H
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
dwcroninAuthor Commented:
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)
Teylyn:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.