Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-24-2020, 02:47 PM
PomDave PomDave is offline Cell formula error? Windows Vista Cell formula error? Office 2010 32bit
Advanced Beginner
Cell formula error?
 
Join Date: Sep 2011
Location: Australia
Posts: 43
PomDave is on a distinguished road
Default Cell formula error?

Hi All,


I'm following the Covid numbers from virusncov.com and copying their data into my own sheet for analysis. One of the columns has figures like +43,852+0.5%


Unfortunately Excel doesn't recognize this and will not place a single figure in the cell. Is there away of changing the starting + to an = sign, thus making it a number.


Best Rgds,


Dave Robinson
Reply With Quote
  #2  
Old 10-24-2020, 02:51 PM
PomDave PomDave is offline Cell formula error? Windows Vista Cell formula error? Office 2010 32bit
Advanced Beginner
Cell formula error?
 
Join Date: Sep 2011
Location: Australia
Posts: 43
PomDave is on a distinguished road
Default

By the way, I do not use any of the data gathered by virusncov.com for anything other than my own curiosity and interest.


Best Rgds,
Dave R.
Reply With Quote
  #3  
Old 10-24-2020, 10:23 PM
Purfleet Purfleet is offline Cell formula error? Windows 10 Cell formula error? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

How are you getting the data into excel? Power query or a simple copy and paste?

Attched i have done a power query version, using the second plus as a delimiter (couldnt get it to delimit on the line feed, but i am no expert) - this should be able to be refreshed daily when the new data is availible.

the Second sheet is the text to columns version - probably the most simple, just insert a column and text to columns

the third sheet is using formulas. this is possible but when i copied and pasted the data i got a few different formats, i dont know why but it would take a few ifs to get it right.
Attached Files
File Type: xlsx Cell formula error_Purfleet.xlsx (261.7 KB, 8 views)
Reply With Quote
  #4  
Old 10-25-2020, 01:57 AM
Jules90 Jules90 is offline Cell formula error? Windows 8 Cell formula error? Office 2013
Novice
 
Join Date: Apr 2019
Posts: 24
Jules90 is on a distinguished road
Default

Hi Dave,

I’m not exactly sure how you want the figures to display in columns – whether the two example figures you have given e.g. 43,853 and the 0.5% are required to be in separate columns or together in one column?
Anyway, going on the assumption first that its two separate columns you want to format, here goes:
1. Select all the cells or the entire column you want to enter the numbers into
2. Right-click on the selected cells and choose ‘format cells’
3. From the Format Cells dialogue box, on the Number tab, click ‘custom’
4. In the ‘type’ box, above the drop down list, clear it and enter: +_-*(hit the space bar once) #,##0
5. The plus sign is left aligned unfortunately, but putting it right next to the number knocks the thousand comma out of the correct place!
6. To make the column look tidier, you can always narrow the width which brings the + sign nearer to the number.

For the column containing the percentage number:
1. Again, select all the cells or the entire column you want to enter the numbers into
2. Right-click on the selected cells and choose ‘format cells’
3. From the Format Cells dialogue box, on the Number tab, click ‘custom’
4. In the ‘type’ box, this time enter: +_-*(hit the space bar once) 0.#%
5. The plus sign is again left aligned, but just narrow the column width to bring it closer to the number.

Therefore, when you enter just the numbers, the plus sign will automatically appear in front of the figure and the thousand comma and percentage sign will also be entered automatically. With the percentage figure you still have to enter the decimal point e.g. 0.5 otherwise if you just type the 0 and the 5 it displays as 5%.

If you want the entire string you wrote e.g. +43,852+0.5%, together in one cell, then enter in the ‘type’ box: +0+0.#% The only problem with this I found is that the thousand separator comma doesn’t display. To give the two numbers more space between each other in the column for ease of reading and understanding, you can enter in the 'type' box: +0*(hit the space bar once) +0.#% You can then widen the column as much as you want to drag the figures to opposite sides.

Hope this is of some help.
Reply With Quote
  #5  
Old 10-25-2020, 02:39 PM
PomDave PomDave is offline Cell formula error? Windows Vista Cell formula error? Office 2010 32bit
Advanced Beginner
Cell formula error?
 
Join Date: Sep 2011
Location: Australia
Posts: 43
PomDave is on a distinguished road
Default

Hi Purfleet,


I'm a pretty basic user of Excel so it was a straight out copy paste set up. I've never used any of this stuff before so Power Query is totally new to me. I did the basics to start with but couldn't see how to split the columns up. Going to look deeper into this for it seems to be the way to go, get back to you to let you know how I get on.


Best Rgds,
Dave R.
Reply With Quote
  #6  
Old 10-25-2020, 02:46 PM
Purfleet Purfleet is offline Cell formula error? Windows 10 Cell formula error? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Are you using office 2010 still?



If you are pretty basic i would actually go with the text to columns version


Just highlight the column you want to split, click text to columns and use space as the delimiter
Reply With Quote
  #7  
Old 10-25-2020, 03:57 PM
PomDave PomDave is offline Cell formula error? Windows Vista Cell formula error? Office 2010 32bit
Advanced Beginner
Cell formula error?
 
Join Date: Sep 2011
Location: Australia
Posts: 43
PomDave is on a distinguished road
Thumbs up

Hi Purfleet,


My Excel is actually 2009 but I found the way to split the column into two. Now that makes things a lot easier, much better than going through the whole list one cell at a time.


Thanks for the tip, put that one into the memory bank for sure.


Best Rgds,
Dave R.
Reply With Quote
  #8  
Old 10-25-2020, 10:40 PM
PomDave PomDave is offline Cell formula error? Windows 10 Cell formula error? Office 2010
Advanced Beginner
Cell formula error?
 
Join Date: Sep 2011
Location: Australia
Posts: 43
PomDave is on a distinguished road
Default

Hi Purfleet,

No, I'm much more updated than Vista, should have updated my stuff ages ago. I'm using Windows 10 and Office365. As I said, I'm pretty basic in my use of Excel and since retiring have no real need for too much and barely scratch the surface as to what it can do, so I'm grateful for any help when needed.

Thanks again,

Best Rgds,
Dave R.
Reply With Quote
  #9  
Old 10-29-2020, 04:16 AM
amritabansal amritabansal is offline Cell formula error? Windows 7 64bit Cell formula error? Office 2007
Novice
 
Join Date: Oct 2020
Location: Gurgaon
Posts: 2
amritabansal is on a distinguished road
Default

Error in Excel appears if you're running a formula that is referring to a cell that doesn't exist.
Attached Files
File Type: xlsx ERROR IN EXCEL.xlsx (9.7 KB, 6 views)
Reply With Quote
  #10  
Old 10-29-2020, 05:43 AM
Purfleet Purfleet is offline Cell formula error? Windows 10 Cell formula error? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Quote:
Originally Posted by amritabansal View Post
Error in Excel appears if you're running a formula that is referring to a cell that doesn't exist.

Hi amritabansal


Is that a question relating to this thread?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
LOOKUP Formula error but WHY??? Haga Excel 1 04-17-2019 11:13 PM
Cell formula error? Entering a column value in a cell, to be used as part of a reference in a formula in another cell paulkaye Excel 4 02-26-2017 04:18 AM
Error handling in formula - suppress 'Syntax Error' Cosmo Word 7 02-24-2014 07:08 AM
Cell formula error? Formula in cell b1 using cell a1 if a1 is over certain number pumkinbug87 Excel 5 12-03-2013 12:34 PM
Change formula cell range based on cell value Scoth Excel 4 10-25-2012 07:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft