Excel Help

[marooko]

=B12-C12-D12

But when I change anything in those cells, nothing happens in the cell containing the formula. This has never happened to me before, why now? Simple ass formula. WTF?!

[PlayaDelWes]

Sometimes (not sure why) the setting for Automatic Workbook Calbulation changes Manual. Go into Excel Settings -> Formulas -> Calculation Options and make the change back.

Another reason for this is the number format for the cells. If it's set to "Text" for some reason, it might not calculate.

[marooko]

Settings. Thank you. Never changed anything there, didn't think to look.

[Goatmeat69]

God you're dumb Marooko.

[marooko]

Everyone knows the settings will change on their own except me. I should wear a dunce cap for that one.

[fatbastard]

Maybe you got a bad batch of Excel

[jackstraw94086]

It can happen when you open a file from someone else who had their calcs set to manual.

[ThatGirl]

In a beginner level one day Excel 2010 course I took voluntarily to better navigate budget Spreadsheets we use at work, the instructor gave us the task of trying to create this formula. How the fuck can this be beginner level? Tomorrow is the advanced course. Am I crazy or is the instructor totally fucked?

="("&LEFT(H1,3)&") "&MID(H1,4,3)&"-"&RIGHT(H1,4)

[locachica73]

I consider myself pretty good with excel, but have never had to do a formula like that. I did when I took lotus123. I tried to copy your formula into excel to see what it did, and it froze my program. Your instructor is totally fucked. :-/

[insbordnat]

That's a fairly basic formula, but different people have different excel needs. I'm often times dumping large amounts of data and doing vlookups, pivots, etc. and in order to "mask" the data I need to use those referential formulas.

I don't think your instructor is that off base. More advanced will probably be pivots, vlookups, match/index, etc. I've been using excel for over 10 years on a heavy daily basis and I still consider myself an intermediate user. Most advanced users I know are creating VBA code and other wacky shit.

[locachica73]

The basic class I took was mainly just how to navigate around, how to use the basic point and click functions in the tool bar and how to format. Even the intermediate class was somewhat basic teaching filters, graphs, etc. I use to know how to use pivot tables & macros, but haven't had to use them in my career so I have lost the knowledge along the way. Sadly, I can wow my bosses by my ability to drag and drop, if I could show them how to use a pivot table they would probably think I am a sorcerer.

[ThatGirl]

I haven't learned filters but we did links, charting, formatting, protecting, a bunch of shit. Need more course work. The instructor was a dip shit.

[locachica73]

Filters are probably my favorite thing about excel. I work for a company that loves logs. We joke that we need to have a log to track all of the logs. Filters are awesome when companies want to keep 5,000 lines of data in the same spreadsheet.

[shakermaker113]

That formula translates a phone number into the standard presentation. If that looks insane to you you've got a long way to go.

[locachica73]

Why wouldn't you just format the cell to a phone number? That is a whole lot of extra typing to do what can be done with 2 clicks of the mouse. Was his mouse dead?

[ThatGirl]

That formula translates a phone number into the standard presentation. If that looks insane to you you've got a long way to go.

Yes this is what it was supposed to do. But it was an entry level class, and a friend of mine that is an Excel expert said she'd never seen it either. I am fully aware that I have a lot to learn but I wasn't the only one in the class that was struggling with this, as it was one of the first formulas we created.

Why wouldn't you just format the cell to a phone number? That is a whole lot of extra typing to do what can be done with 2 clicks of the mouse. Was his mouse dead?

He didn't tell us this shortcut, but he was a pretty old guy, a computer science type math geek, seemingly more interested in doing fancy arithmetic calculations than teaching us how to use this application in a practical way.

[insbordnat]

I think he was just trying to show the left/right/mid formulas in a useful way. There's a lot of ways you can use them and the "&" to concatenate data without using CONCATENATE.

[ThatGirl]

Yeah. Every time he said that word I think I had a little stroke.

[shakermaker113]

I'm not sure this class is for you.

[ThatGirl]

It was only two days and I finished it, but I need to take the advanced course next month. I just hope this same guy isn't the instructor.

[chairmenmeow47]

i have a worksheet. i want to count all of the Y's in one column that also meet the criteria of having "X" designation in another column. so if there are 20 Y's in the column, only 2 of those may have the X designation. so the answer i want is 2, not 20.

i have been playing around with COUNTIF formulas and can really only get the 20 answer, not the 2. help please!

[chairmenmeow47]

figured it out, apparently my brain was in full retard mode yesterday when i typed the formula

[jackstraw94086]

hopefully you used a SUMPRODUCT for that. tricky formula, but most elegant solution to that problem.

[fatbastard]

I added an N/A just in case neither letter appears in the 2 columns.

=IF(SUMPRODUCT((A2:A18="Y")*(B2:B18="X"))=0,"N/A", SUMPRODUCT((A2:A18="Y")*(B2:B18="X")))

[insbordnat]

SUMPRODUCT is great but I hardly use it because it's a system drag with larger spreadsheets. Typically I use countifs.

[chairmenmeow47]

i used countifs.

[jackstraw94086]

Countif's great if you're sure you wont have more than one or two criteria, but if any more pop up then it starts to get messy to modify and keep straight.

[captncrzy]

Ok, I need help with a formula. I need a function that looks at the value of column a and the value of column b and matches it with an address in another spreadsheet. For example:

I have an existing spreadsheet called Distro List. The list contains a list of cost centers, each cost center's location (some cost centers have more than one location assignment) and the shipping address for each:

Cost Center	Location	Shipping address
1234	PHX	999 W Elm St, suite 75
5252	PHX	997 W Elm St, suite 4
5252	TUL	653 S 75th Ave
5252	DFW	333 W Bacon Rd

Monthly, I receive a list of employees that need to have things shipped to them. The list contains the employee's name, cost center and their location.

Cost Center	Location	Name	Shipping Address
1234	PHX	Leonard Cohen
5252	PHX	David Bowie
5252	TUL	Prince Princeton
5252	PHX	Leon Russell
5252	PHX	Pete Burns

What I need is a formula that takes each employee's cost center + location, goes to the Distro List, and uses that information to look up the address. The end result should look like this:

Cost Center	Location	Name	Shipping Address
1234	PHX	Leonard Cohen	999 W Elm St suite 75
5252	PHX	David Bowie	999 W Elm St suite 4
5252	TUL	Prince Princeton	635 S 75th Ave
5252	PHX	Leon Russell	997 W Elm St Suite 4
5252	PHX	Pete Burns	997 W Elm St Suite 4

[captncrzy]

someone help me!!!

[nine day brawl]

first, concatenate the Cost Center and Location columns =Concatenate(Cost Center Column, Location Column) so the output should read "1234 PHX" on both spreadsheets. then you can perform a vlookup and you should get your address.