Do you vlookup?

Atoms for Peace

Excel Nazis are the worst! :pac:

Jayop

Atoms for Peace wrote: »

Excel Nazis are the worst! :pac:

=IF(Thread,"do you lookup?"=1 page, (likelyhood of comparison to hitler =10%),IF(Thread,"do you lookup?"=2 pages, (likelyhood of comparison to hitler =20%)......

Are Am Eye

Patww79 wrote: »

This post has been deleted.

F2 and enter.

Thanks. I'm here all week.

LarryGraham

Unless you write macros for recreational use you're a fcuking amateur.

Are Am Eye

You need to fx*k windows. Fx*k it right in the API.

Xaracatz

pickarooney wrote: »

It's all fun and games until you move abroad and all the formulas have different names.

****. That. Noise.

SVERWEIS.

SVERWEIS?

https://www.youtube.com/watch?v=41lZmGcRWHU

bluewolf

bluewolf wrote: »

Vlookup hlookup index match, but my favourite is still sumproduct(--(lookup column) (lookup row) (sumrange))
Not to be confused with normal sumproduct, it just does a vlookup or hlookup or both and sums the results instead of only returning the first result. Has to be the handiest

very inefficient - if you have lots of cells with this formula slows down excel to a crawl on recalculate.

glasso

glasso wrote: »

very inefficient - if you have lots of cells with this formula slows down excel to a crawl on recalculate.

Never had enough cells for it to be an issue but will bear in mind!

Are Am Eye

LarryGraham wrote: »

Unless you write macros for recreational use you're a fcuking amateur.

You've a great post history

Lu Tze

I'm sure there is a a more simple way of doing this, but I often use concatenate to build arrays of formulas, and the copy and paste values to replace the concatenate formula with the resulting formula. Find and replace all the "=" to make them live

Pwindedd

Shenshen wrote: »

Google's your friend. Find yourself an example of what you'd like to do or calculate, and google it.
Nearly everything I know about Excel I learned through this, or through trial and error

This...

Apart from an entry level course i did yonks ago, all my excel is self taught. If i don't know, i google it and practice. Only learn what you need to know or use regularly. And keep quiet about it. You really don't want to become the go-to person in the office.

My most used macro is the simplest little thing and it blows (simple) minds regularly.

CTRL S colours a cell green, CTRL D colours a cell yellow - (useful if you do a lot of manual reconciling)

Odhinn

Pelvis wrote: »

This one is aimed at the office workers.

Do you know how to vlookup? If you work with excel on a daily basis, shouldn't you know this very simple function?

I'm sick of getting emails from people who have worked with spreadsheets for years asking me to do this stuff, people who I would have thought were reasonably tech savvy.

What gives?? Are they lazy or stupid?

I remember sitting in an office where people were sitting in front of pc's, each loaded with Excel and them using a 'printing' calculator to total lists easily spoolable into that program. I suggested using Excel, and became "Worst Bollox Ever", because apparently an extremely long working day is preferable to trying to being a smart arse and trying to do things a bit faster and fucking off somewhere near actual quitting time.

FixdePitchmark

Vlookup would be fairly basic relative to complex nested If functions.

Excel is simply incredible - the stuff I have seen done with it is mind boggling. To be honest it becomes a hobby for some people. I've seen people spend months solving sometimes problems that are more a personal challenge than anything to do with work.

One guy I know spent weeks on a problem and it was to do with a leap year bug - There is a leap year every year whose number is perfectly divisible by four - except for years which are both divisible by 100 and not divisible by 400

The real power of excel is dealing with very very large amounts of data - for example some applications you have 100s of figures coming per second. In fact a few older versions of excel didn't have enough cells to deal with what many people were trying to do - solved at this stage.

Someone may turn around and say - there is better out there - but I guess most people got cornered and skilled up in excel and it helps that you have it at home too.

Regression Analysis is just a dream with it.

Anyway - this is After Hours.

Excel has dumbed down needing to do maths. But understanding what you can do with these powerfull tools is the new maths i guess.

Next step is to send all this data to smart phones in an easy way - a graphical way. That is the sort of stuff we will see in next few years way more. Real time smart data.

Cookie_Monster

if(iserror(vlookup(...

The Hill Billy

pickarooney wrote: »

It's all fun and games until you move abroad and all the formulas have different names.

****. That. Noise.

Or they have the same names, but use ; instead off , as the separator. Fffffuuuu...!!!

To be honest the real skill in Excel is to be able to do complex things if needed and yet have it designed cleverly so that other people can understand it quickly.

Lots of nested formulas (split them up instead) and complicated vba macros should only be used when absolutely necessary.

Are Am Eye

glasso wrote: »

To be honest the real skill in Excel is to be able to do complex things if needed and yet have it designed cleverly so that other people can understand it quickly.

Lots of nested formulas (split them up instead) and complicated vba macros should only be used when absolutely necessary.

Nobody should ever understand your work. Do you want lose your job or something.

Are Am Eye

Are Am Eye wrote: »

Nobody should ever understand your work. Do you want lose your job or something.

I know this is after hours but you're doing it wrong

LarryGraham

Are Am Eye wrote: »

You've a great post history

I'm building up to something big.

Valmont

Excel is for old ladies - the real analysts do it all in R.

Valmont

FixdePitchmark wrote: »

Next step is to send all this data to smart phones in an easy way - a graphical way. That is the sort of stuff we will see in next few years way more. Real time smart data.

Microsoft is way ahead of you. Power BI lets you build live graphical dashboards that are specifically formatted for smartphones.

No Excel needed.

Esel

Lia_lia wrote: »

Lol. I was talking my my OH about vlookups the other day (I know right). He uses them in his job the odd time and he told me for ages he used to call them v-card lookups. I had to explain to him that "losing your v-card" is slang in the states for losing your virginity. He was wondering why people were looking at him funny when asking for help with v-card lookups!

Tell him about the new vhookups feature.

x43r0

Valmont wrote: »

Excel is for old ladies - the real analysts do it all in R.

Python FTW

DavyD_83

I have just discovered hlookup exists; thanks for that.

No more need to transpose before vlookup (not that I would have done that, that would be silly)

The J Stands for Jay

Just talking about this in the office this morning. There's a large number of people here who don't know the magic that starts when you type '=' into a cell. Yes, I've seen people use a calculator and type the result into a cell to get a total...
Some people just think it's a tool that lets you type in columns easily (they also aren't good with Word).

SnakePlissken

*Stumbles into thread, takes a look around*

Yep... I'm an idiot.

*Quietly retreats*

x43r0

Second the Jupyter recommendation. We also use Zeppelin Notebooks. Both offer good options in terms of languages and visualization

Christy42

Permabear wrote: »

This post had been deleted.

While I love python I presumed they were talking about the more accounting side of finance. Excel works well for that. Excel is great for simple plots/having the data in a handy format. If you are going for the big data stuff excel is a pile of dirt. Similarly if you are going for anything with a large amount of complications. In some cases I feel that is being harsh on the dirt.

Excel is a nightmare to maintain large projects in. Debugging vba/ excel formulas written by someone is just horrific. It just does not seem to encourage good practices. Random copy/pastes all over the place, selecting variables by cell and no tests involved. *shudders*.

pumpkin4life

Excel for quick/basic data manipulation/graphs etc.

Python/MySql for advanced, very specific data manipulation with a load of (1 million rows plus) data.

R for analysis and nothing else.

Stay the hell away from VBA if at all possible.

Valmont

I find Python is best for data acquisition, transmission, and storage but R is unparalleled for actual analysis. However I'm increasingly finding I'm trying to cut R out of the pipeline if I can because it just makes more sense to keep everything flowing smoothly in Python if possible -- it helps that writing Python is much more enjoyable than writing R, which can be a bit all over the place sometimes.

You'll often read articles saying Python is the future of data analysis but it's just not that simple: both R and Python will coexist as there is ample room for both. Microsoft have invested heavily in R, creating their own enhanced Microsoft R platform that can integrate with their .NET framework. Recently I thought I was going to have to bite the bullet and learn some Excel and Dax to work with Power BI effectively but lo and behold a recent upgrade integrated R functionality so now all that data wrangling can be done in the application itself fewer lines of simpler R code. And the new version of SQL server will have both R and Python support!

While we're on the subject, I tried to learn R first having no programming background and hit a wall very quickly. I went away and learned some Python (through Coursera) and the R made much more sense after that.