What's your favorite Excel function?

Kinda tough for me to explain...me no good teacher
Basically you index the shit you want to pull and match it to the criteria

Vlookup just returns a value from a column based on another value.

Ooh, COUNTIF() is good too.

20 nested IF statements

Even better if used with 20 indirect lookups. Fucking nightmare shit.

>using Excel
Kek, brainlet detected. Excel is a bloated mess. Whenever I need to use this shit, I just parse out the xml, manipulate it, and jam the manipulated numbers back in the file, hoping it doesn't become corrupted (excel can fix the corruption most of the time without the data disintegrating). For example:
>implement a data aggregator in VBA to parse data from 500 files
>takes 30 minutes to run
>implement this same data aggregator in python
>takes 22 seconds

Vlookup & Match

Now you replace the "row number" with a MATCH function that outputs the row number.

...

You obviously don’t work in finance brainlet

VLOOKUP

index + lookup

IFERROR all day long

FLOOOOR()

Well it's not (SUM)

Nested ifs. I once had 9 ifs in one formula
I couldn't see shit

=if(if(if(if(if(if(if(if(if))))))))

Just bought 100k XLS thanks for the tip user

ROUND()

millenials gtfo

>vlookup
Learn to index match, Grandpa.

>using vlookup or sumif in the current year +2
>not index or sumifs

shiggy diggy muh niggy

EOMONTH
SUMPRODUCT
SUMIFS

and this with negative num_digits and of course this

delet

=SUMPRODUCT()

Also Excel sucks
Julia is the future

Same. Holy shit i knew how to use excel. I forgot everything. Also knew visual basic 6. Created trainers back then.
How did i get so stupid

Now I fell intellectually superior for using Python and R for statits

=A1+B1+C1+D1

it is. don't know this obsession with lelxel at all

Sumif is better if you only have 1 criteria. Vlookup is better if you are looking left to right. I can do it all.

>t. MS certified Execl Expert

SUM() you can do anything with it. I don’t know what you use excel for but there are dozens of functions (for example PMT, MIN, MAX, AVG) that could replaced with SUM and arithmetic.

=+VLOOKUP
=IF

Basically 50% of my job are theses two formulas, some of the benifits of being in your 20's in a 50 average business. They look at me as if I'm a wizard with my shitty formulas, conditional formating and the copy pasted macros from google..

Tell me the benefitas of INDEX vs VLOOKUP

>boss asks you how to do something
>google it in 5 seconds and do it
>"nice work, I never would've figured that out"

Idk if it exists in excel but i like random(random(360))

You can look up values to the left and right of you match cell. The syntax for vlookup is retarded. Index match performs better on large sheets.

LEFT ()

I like "clear contents" i use it a lot when looking at my coin investments

Nice, I work with data sets of 70000 rows x 20-30 columns, needless to say, it's often slow..
And the left to right limitation is a constant hassle.

I know what I will spend the first 2 houra tomorrow googling

Best thread i've seen on biz

Does using VBA count?

Only if you name your function DONKEYBALLS().

This

SendKeys "%{F4}"

Sub DONKEYBALLS (ballsize as integer, scrotumcapacity as float)

VLOOKUP MASTERRACE

>excel
Yuck

Do you ever use TRIM() though

Learn sql you faggot

Definitely SUMPRODUCT().

Honorable mention to good old nested IF(), but honestly, anything that isn't an array formula need not apply. It's just a whole different league of usefulness.

CONCATENATE () is godlike

SUMPRODUCT used to be good until SUMIFS and COUNTIFS functions were introduced

array formula masterrace

Okay guys sort of newfag here, don't blame too much. Work in a casino, soon we will have few Risk Analyst vacancies and I do want to join, but first I need to understand how to become excel guru in order to do stuff with numbers. Due to previous jobs had no reasons to study excel, and this position will require of monitoring players behavior for 200+ tables with 5000+ online customers.

Point me at some books or courses to start with. How to get into this? Data science / statistics on youtube/coursera, then to find datasets and crack the numbers before I feel confident, then ask for test cases to see if I'm ready?

Today()
Sum()

we programmer now

All you need to know is how to calculate an average and possibly a percentage

Lately, it's Been WorkingDays()

This let's you calculate days between two dates, but it eliminates weekends and a user-defined list of holidays.

It's handy when calculating the number of trading days between now and when options expipre

waste of time. just calc the number of weeks and multiply by 5 for approximation. if your doing anything with options thats of importance, you wouldnt be using excel. btw enjoy getting assblasted by a dealer with better information

Excel is good with options.

I simply go to my broker option page, drag and copy, then go to excel and hit a button to read and translate everything into various pages.

The problem is only as good as what you use it for.

I have some concerns that millions of people in offices doing only this. Don't know about other industries, but in gambling we do have some very sophisticated approaches, since I'm working not in a landbased but online casino with 10+ different games (BJ, roulette, pokers, etc).
You really want to tell that all what is needed is to keep spreadsheets in order and sometimes calculate avg?

Oh wow, those are indeed neat. Never even realized they existed. I can definitely see the utility. Thanks for the pointer, although I'm not sure I'm going to bother switching. It's not elegant but I'm just so used to expressing criteria as (X:Y=Z) and using them as 1s and 0s in giant products. At a glance sumifs doesn't seem to do anything functionally different, just the same in a bit more structured way.

Still neat.

Vlookup is a left join. Describe it in SQL terms and you'll have the interviewer eating ut of your hand.

I think it all boils down to that really. What are you actually calculating? How much each person spends on a given game? How long different games retain different people that spend different amounts?

When people get JUSTd by one game where do they flock to?

What kind of person knows when to quit?

Gimme a % and average and i'll analyze it for you

user, what you need to do is to research up the history of big data in casinos. Casinos use reward cards to track how much money a person loses, and uses this to give them vouchers and incentives to stay in the casino right when they are about to hit their breaking point in terms of losses. this keeps them playing a little longer, allowing the casino to earn some more money.

Do the reason, watch some excel tutorials online, and discuss both of these things with the interviewer and you should have a decent shot

Yeah I used sumproduct all the time as we had excel 2003 at work for a very long time, but I ended up switching over as many of the spreadsheets i create end up being passed on to others and i think it's a bit easier for others to understand what's happening.

meant for

>VLOOKUP in 2018

ffs. Hlookup is occasionally alright

Hey, what if you use it to create a snazzy spreadsheet to keep track of your friends on the AOL

NOW()
Name literally ONE thing you can’t do with this. One.
>you can’t

Feb 5, 2018 19:24

Yeah, I can see that. Now that I'm thinking about it - I'm assuming with sumifs you can also avoid the double-minus shenanigans you sometimes needed with sumproduct if excel throws a formatting hissy-fit? That would be an actual improvement over sumproduct I could get behind.

I think there's something wrong with your computer. I tested this function and came up with a different answer

Shit, I think I forgot to delete system32

Yep, that's probably it.

Actual Casino Analyst here (pic related). I highly doubt any of your models will be that complicated. The expected odds for each game are already calculated & easily available on websites like Wizard of Odds.

Theoretical Win is calculated by (Player's Average Bet)*(Player's Rate of Play)*(TIme Playing)*(Game Odds)

With Game Odds maybe being switched out for a Rolling Average of the Game's hold rate since most players don't follow optimal strategy.

Good string functions.

How about favorite python functions?

pd.normalize for datetimeindex objects for sure

Keep finance related please.

Yep that's right. AVERAGEIFS is also new and occasionally useful -- I'm waiting on MINIFS and MAXIFS, but for now to perform those functions an array is still required.

use ampersand

Well, as I know for now, there about 2-3 people on shift who monitor all gambling activities on 200+ tables in several countries which means they have to know betting strategies, patterns, possible outcomes, payouts, keep in mind possible dealers mistakes, possible equipment issues and use all this to look up for suspicious activities, and when there are any of those they need to mitigate them. At least this is what job description is. It's not cozy office job when you just calculate ammount of coffee your company sold last month.
Problem is I'm working night shifts and my schedule is fucked up so I can't just hang around to see office guys and ask about more details, and my current supervisors refuse to provide any info because they know if I will get promoted they will have very big problems since I do have reasons to make them fired.
In your early 20's your life is far from perfection, so I had other shit to deal with, now when it's sort of finished I'm planning to put more effort in a careers, so I'm just asking where to start.

>How about favorite python functions?
definitly the urlllib module. its what I use to make my dubs and trips scripts

>you're working in excel and scratching your head trying to remember a function to calculate something
>chainlink appears
>jason parser steps in and gives you an excel plugin
>all functions calculated by chainlink node
>cell 47 is just a plugin

Your image.

Is that a monte carlo price estimate routine?

If you don’t know how to code you’re just an excel monkey

holy shit thank you. I only ever use vlookups and this made index match look so understandable. Props breh

Shot is decent, indeed, but since our company is sort of Coca-Cola in it's industry requirements set very high, and I just want to do more preparations while I'm under radar to make sure other people will have not much of time to maneuver when I will show up. At least if casino will not make it, we have banks and other IT companies who constantly trying to get peoples from ours.

Cool, then I'll probably bother to mess around with it a bit to feel it out. Thanks again!

yep, just using a normal distribution return process assumption

disappointed that you make that post and don't get dubs. Check em

Okay, thanks.
Generally I just need to get used to these formulas to be able to act fast, then get used to the software and keep looking for what's happening and fill / send reports? That's all?

you need to turn calcuation to manual temporarily, as well as disable screen updating and it'll fly.

It seems like you are leaning more towards making sure there aren't people taking advantage of loopholes in the software that could cost the house money, this is a different focus than what casino analyst user was saying about getting info about people to keep them playing

If you want to keep the house edge, i'd start with a flag that lets me know when a particular game is below a certain thteshold of the average money paid out vs house money

Use it on some live data and then try to figure out the reason why the house edge was lowered, you may be able to spot patterns than are more than just dumb luck

Then you can write a code that identifies these patterns and deals with them accordingly

Then again i am still unsure if you are monitoring real live data or just analysing large amounts after the fact. Casino analyst user above has some good advice

From your description here:

It sounds like you'll be in a mixed role between Analysis & Survellience. In that case Excel skills will probably be even less necessary since you'll be focused primarily on suspicious patterns & behavior if you're doing real time monitoring like the description makes it out to be. I'm in FP&A, so I don't do theft/cheating detection. I just use that to explain why we undershot forecast when it comes out that the swing shift dealers have stealing.

What is this sorcery

Thanks for response. Sounds quite complicated, but still fair. Anyway I'm not looking to join this position in next week like just give me this job and I'll learn everything in 2 days. Probably going to spend few months studying poker and gambling strategies more deeply, then will have a talk with Risk Department guys to ensure if I'm on a right way and let them evaluate my weak sides I will need to improve. Not going to be easy, but I'm okay with it.

>It sounds like you'll be in a mixed role between Analysis & Survellience

Well, yea, it's more about real-time monitoring & daily/weekly/monthly reports. We provide live studios for 300+ casinos, and if something happens we need to directly contact our customer (casino) to let them know some Ahmed is making something very unusual and making them lose huge money. As far as I know it means we are monitoring 24/7 what's going on. If everything is fine just fill the shift report, if something actually happened you need to inform Senior Risks & casino directly, and when you did you just continue monitoring, seniors will deal with casino and you maybe will need to provide some logs.

Will I regret my career choice? I'm currently 25, so there's not much of fascinating jobs available in EU country with less than 2mil population and GDP less than 50 billion euros