Trouble Formatting Cells in a specific way
Hi all.
For a maths module at university I have developed a model that accounts for range deviation in a specific problem. However, I cant get Excel to show this answer in the desired format.
I want it to say 'Value in B2' 'plus minus' 'Value in B3' in one cell.
I have attached a workbook better showing the problem.
Thanks
Use this:
Formula:
=B2 & "±" & B3
The ampersand & is a concatenation operator, which joins together different items (values, text, cell references, whatever). Anything which isn't a number or a cell reference needs to be in inverted commas.
The above formula will give (using the figures from your sample sheet): 10±2
If you want spaces before and after the ± then just add them inside the inverted commas:
Formula:=B2 & " ± " & B3
Hope that helps.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Thank you. That's really helpful.
In cell b3 and b2 my actual values are like 5 decimal places (10.29172). If I format these to 0 decimal places, it still appears as five decimal places in the bottom cell.
B2 = 10.29172 -----> 10
B3 = 1.9892832 ----> 2
B5 = 10.29172 ± 1.9892832
Ideally B5 would say 10 ± 2
Thanks
Try
=FIXED(B2,0)&" ± "&FIXED(B3,0)
In that case, include the ROUND function, like this:
Formula:=ROUND(B2,0) & " ± " & ROUND(B3,0)
ROUND(B2,0) rounds B2 to the nearest number with 0 decimal places, so 10.29172 becomes 10. ROUND(B2,1) would round to the nearest 1 decimal place, to give 10.3, etc.
x.5 will round up. If you specifically want to always round up or round down, then use ROUNDUP or ROUNDDOWN instead. e.g. ROUNDDOWN(B2,0) will give 10 for 10.0000000000001 to 10.9999999999999.
TryFormula:=TEXT(ROUND(B2,2),"0")&" ± "&TEXT(ROUND(B3,2),"0")
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Thanks everyone. All methods worked.
Much appreciated.
Glad to help & thanks for the feedback
You're welcome, glad we could help and thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks