Any excel wizards?

[KingstonSeagull]

Step 1: NO MACROS

Macros literally save me about 4 hours work a day

 

[Publius Ovidius]

I was always a lotus 123 man. Excel came along and the companies I worked for, even though I was the financial person at a few of them would never send me on courses, so I had to take what I knew in lotus and transpose it to excel ....trouble is I could never work out how to use the formula side so a lot of my sheets were very basic.

It's only now in my 58th year trying to analyse change ticket data, producing graphs to show failures etc that I realise I should have pressed even more to get more training.

Again thanks to all who have helped with my initial query.

 

[KingstonSeagull]

I was always a lotus 123 man. Excel came along and the companies I worked for, even though I was the financial person at a few of them would never send me on courses, so I had to take what I knew in lotus and transpose it to excel ....trouble is I could never work out how to use the formula side so a lot of my sheets were very basic.

It's only now in my 58th year trying to analyse change ticket data, producing graphs to show failures etc that I realise I should have pressed even more to get more training.

Again thanks to all who have helped with my initial query.

I have never been on a training course, it has been all google and getting myhands dirty.

 

[Bad Ash]

I have never been on a training course, it has been all google and getting myhands dirty.

I do wonder how people coped without Google. Like you the vast majority of my skills (Excel and now SAP) have come from jumping in getting my hands dirty and an awful lot of Googling.

Training courses while useful, never quite replicate what you learn when you have to pick something up and apply it to solve a real life problem, rather than a meaningless training exercise.

 

[ForestRowSeagull]

Love me some excel. We've just got our bloomberg terminals linked to our excel and its opened up so many new ways of wasting time fannying around with formulas

 

[KingstonSeagull]

Love me some excel. We've just got our bloomberg terminals linked to our excel and its opened up so many new ways of wasting time fannying around with formulas

I bloody love what you can do with excel and bloomberg. I make things just for fun sometimes!

 

[BBassic]

As an aside, I am convincing myself that contributing to this thread counts as WORK.

Head on over to StackOverflow. Loads of questions on there for answering

 

[clapham_gull]

Macros literally save me about 4 hours work a day

.. and take someone 4 weeks to sort it out after you leave

Seriously though - it's about using macros when basic excel functionality is better and easier.

And creating templates and models that everyone understands.

 

[KingstonSeagull]

.. and take someone 4 weeks to sort it out after you leave

Seriously though - it's about using macros when basic excel functionality is better and easier.

And creating templates and models that everyone understands.

haha if I leave they are screwed! No one understands my macros and our team need them for about 90% of what we do!

 

[El Presidente]

ALT+=+= is my favourite Excel formula. It covers about 90% of what I do.

 

[FatSuperman]

ALT+=+= is my favourite Excel formula. It covers about 90% of what I do.

Wow. I did not know this shortcut. More please.

In fact, any nice windows / office shortcuts please. NOTE: Points will be deducted for crap or obvious ones.

 

[El Presidente]

Wow. I did not know this shortcut. More please.

In fact, any nice windows / office shortcuts please.

Alt+=+= works on vertical lists of numbers,horizontal lists of numbers, and lists of subtotals too.

 

[DTES]

My life was changed when I discovered CTRL & [ (open square bracket)

Press it when you're in a cell with a formula and it will take you directly to the cell it's pulling from - even opening another workbook if necessary.

 

[spongy]

You may be on the same course as me in Hove.

Bit of a thread derailment here but....
Not quite. I'm doing an engineering course based at Central Sussex college in Crawley.

According to them apparently my GCSE's are "out of date". Since when did an exam qualification become "out of date" Smells like BS to me but I have to take it on the chin and get on with it.

That's why I'm having to re-do these "functional skills" bits.

I aced the Internet and word processing stuff.

Got 100% in the English language bit and 99% in the written. I've just never had a reason to use spreadsheets since my GCSE business studies. And even then it was very basic.

It's all complete and utter ******** if you ask me. I'm an engineer, I use a machine and don't sit at a desk. Why they think I need to know how to use a spreadsheet is beyond me but hey ho....

As you were gents.....

 

[FatSuperman]

I've just never had a reason to use spreadsheets since my GCSE business studies. And even then it was very basic

How are you getting on? If you need some help there are plenty of very good resources 'on the line'. I've not used this one but it looks well structured and crucially, it is free:
https://excelexposure.com/ (this one is recommended on university of reddit, if that holds any sway)
http://www.gcflearnfree.org/excel2010

 

[spongy]

How are you getting on? If you need some help there are plenty of very good resources 'on the line'. I've not used this one but it looks well structured and crucially, it is free:
https://excelexposure.com/ (this one is recommended on university of reddit, if that holds any sway)
http://www.gcflearnfree.org/excel2010

It's all at a bit of a standstill at the moment.

The test I posted about earlier was just a test to see what level I'm at with what so to speak.

Maths, English and Internet skills are all good. Just spreadsheets I didn't know.

I'm a cnc setter/operator by trade so spreadsheets are alien to me. I don't use a computer at work.

Why I have to be proficient in using Excel is beyond me. But L have to do it as part of the course I'm on so just gotta suck it up and go with the flow.

I'm going to have arrange extra time off work to attend some classes but that's works problem as they pay for it. Just waiting to hear back about the details.

Thank-you for asking though

 

[TheJasperCo]

Anyone familiar with the stats package 'R'? You have the chance to be my new best friend forever.

 

[Eeyore]

Did you put a = at the beginning? so =SUM(...
The = at the beginning indicates its a formula rather than just text.
Still think mine is simpler because it doesn't require ctrl + shift + enter at the same time to turn it into an array forumula:
=sumproduct(value(substitute(a2:a4,"*","")))

Your formula works great except that it returns an error if there are blank cells in the range. Can it be adjusted to take this into account ?