Excel help
pinno
Posts: 52,312
Bit rusty in this department (apart from cooking the books and doing dodgy cash flow forecasts).
I am trying to create a (cycling related) look up table. If you are willing to divulge your e-mail address, please PM me.
I have done the hard bit. The table is there, I need a function so I can use the data created to make a calculation.
Ta.
I am trying to create a (cycling related) look up table. If you are willing to divulge your e-mail address, please PM me.
I have done the hard bit. The table is there, I need a function so I can use the data created to make a calculation.
Ta.
seanoconn - gruagach craic!
0
Comments
-
PM's are busted at the moment, keep up :P
Even if they weren't do you think I'd be stupid enough to let you have my email address.....oh hang on
"Arran, you are like the Tony Benn of smut. You have never diluted your depravity and always stand by your beliefs. You have my respect sir and your wife my pity"
seanoconn0 -
arran77 wrote:PM's are busted at the moment, keep up :P
Even if they weren't do you think I'd be stupid enough to let you have my email address.....oh hang on
OMG. What have I done? :shock:0 -
I sent you an email ... Get in touch if you still need help ...0
-
Crankbrother wrote:I sent you an email ... Get in touch if you still need help ...
Cheers fella, I sent you an attachment.seanoconn - gruagach craic!0 -
pinarello001 wrote:Crankbrother wrote:I sent you an email ... Get in touch if you still need help ...
Cheers fella, I sent you an attachment.
Done (maybe, given I was just guessing what you wanted) ...0 -
Cheers Crankbrother, a cyber pint of 80 shilling is in the ethernet for you. (None of that warm beer shyte or feeble 'Pale ale').seanoconn - gruagach craic!0
-
Have you tried switching it off and on again?I'm sorry you don't believe in miracles0
-
"Arran, you are like the Tony Benn of smut. You have never diluted your depravity and always stand by your beliefs. You have my respect sir and your wife my pity"
seanoconn0 -
-
I hacked into your webcampinarello001 wrote: but I soon hacked out again! :shock: The horror, the horror, the horror Pinno, מלך אידיוט וחרא מכונאי0 -
seanoconn wrote:
I hacked into your webcampinarello001 wrote: but I soon hacked out again! :shock: The horror, the horror, the horror
Was he strutting around in that gimp suit again "Arran, you are like the Tony Benn of smut. You have never diluted your depravity and always stand by your beliefs. You have my respect sir and your wife my pity"
seanoconn0 -
arran77 wrote:seanoconn wrote:
I hacked into your webcampinarello001 wrote: but I soon hacked out again! :shock: The horror, the horror, the horror
Was he strutting around in that gimp suit again
I recon it was worse...... He was out of it0 -
Excel is such a frustrating programme I often need "help" after using it.
Good luck.0 -
jimmythecuckoo wrote:Excel is such a frustrating programme I often need "help" after using it.
Good luck.
Really? Surely only a frustrating program if you don't like numbers full stop. That's not a fault of the program.
If you have any affinity for number work it is an amazingly powerful program.0 -
jimmythecuckoo wrote:Excel is such a frustrating programme I often need "help" after using it.
Good luck.
If you want a frustrating program, try doing Access Macro's.
Excel is superb but like anything - only if you need it.seanoconn - gruagach craic!0 -
Excel has some pretty horrible misfeatures. My favourite is its tendency to convert fields that look vaguely like dates into actual dates, silently corrupting the data. If a naive user has Excel installed and double-clicks on, say, a CSV file (which will usually have an Excel icon), Excel will helpfully open it and apply some pretty brain-dead guesswork to deciding what data type is in each field. Once a text field becomes a date and the file is saved there's no going back - the contents of the cell are permanently changed. Unlike Access, Excel doesn't give you any warning that it's decided 3 from a column of 50,000 text fields should be converted to dates, it just goes ahead and does it.0
-
RDW wrote:Excel has some pretty horrible misfeatures. My favourite is its tendency to convert fields that look vaguely like dates into actual dates, silently corrupting the data. If a naive user has Excel installed and double-clicks on, say, a CSV file (which will usually have an Excel icon), Excel will helpfully open it and apply some pretty brain-dead guesswork to deciding what data type is in each field. Once a text field becomes a date and the file is saved there's no going back - the contents of the cell are permanently changed. Unlike Access, Excel doesn't give you any warning that it's decided 3 from a column of 50,000 text fields should be converted to dates, it just goes ahead and does it.
No, not true. Click on/highlight the the cell(s) or range of cells. Click on Format > Cells > Select 'General' or otherwise from the list.seanoconn - gruagach craic!0 -
pinarello001 wrote:RDW wrote:Excel has some pretty horrible misfeatures. My favourite is its tendency to convert fields that look vaguely like dates into actual dates, silently corrupting the data. If a naive user has Excel installed and double-clicks on, say, a CSV file (which will usually have an Excel icon), Excel will helpfully open it and apply some pretty brain-dead guesswork to deciding what data type is in each field. Once a text field becomes a date and the file is saved there's no going back - the contents of the cell are permanently changed. Unlike Access, Excel doesn't give you any warning that it's decided 3 from a column of 50,000 text fields should be converted to dates, it just goes ahead and does it.
No, not true. Click on/highlight the the cell(s) or range of cells. Click on Format > Cells > Select 'General' or otherwise from the list.
Doesn't always work unfortunately.
Excel stores dates as floating point numbers - integers being whole days etc. If it's allowed to execute the conversion at load-time your spreadsheet will be full of these numbers rather than the original text.
Avoidable, but you need to know your way around the program.- - - - - - - - - -
On Strava.{/url}0 -
DesWeller wrote:Excel stores dates as floating point numbers - integers being whole days etc. If it's allowed to execute the conversion at load-time your spreadsheet will be full of these numbers rather than the original text.
Yes, this. To demonstrate, create a text file in (e.g.) notepad called 'test.csv' that contains the text:
MAR1
Typically, CSV files will be registered to Excel on Windows systems running Office, so double-clicking on the file will open Excel and present you with a spreadsheet where the first cell looks like this:
Mar-01
(or in whatever format Excel is configured to display dates). Changing the cell format to 'text' gives you Excel's internal numerical representation of March 1st (not the original string):
36951
One correct way to import data from CSV files is to use the data import wizard and specify what data type to use for each column (in this case 'text' will preserve the string 'MAR1'), but this isn't obvious to the inexperienced user (because just clicking on the file to import it seems to do the right thing). This is a significant problem for people working with biological data, who might have spreadsheets containing tens of thousands of gene names, a few of which Excel will interpret as dates and corrupt - these keep turning up in major online gene databases:
http://www.biomedcentral.com/1471-2105/5/80
http://nsaunders.wordpress.com/2012/10/ ... t-learned/0
