1. ## Re: Excel Help

=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?!

2. ## Re: Excel Help

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.

3. ## Re: Excel Help

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

4. ## Re: Excel Help

God you're dumb Marooko.

5. ## Re: Excel Help

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

6. ## Re: Excel Help

Maybe you got a bad batch of Excel

7. ## Re: Excel Help

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

8. ## Re: Excel Help

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)

9. ## Re: Excel Help

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. :-/

10. ## Re: Excel Help

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.

11. ## Re: Excel Help

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.

12. ## Re: Excel Help

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.

13. ## Re: Excel Help

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.

14. ## Re: Excel Help

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

15. ## Re: Excel Help

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?

16. ## Re: Excel Help

Originally Posted by 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.
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.

Originally Posted by 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?
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.

17. ## Re: Excel Help

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.

18. ## Re: Excel Help

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

19. ## Re: Excel Help

I'm not sure this class is for you.

20. ## Re: Excel Help

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.

21. ## Re: Excel Help

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!

22. ## Re: Excel Help

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

23. ## Re: Excel Help

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

24. ## Re: Excel Help

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")))

25. ## Re: Excel Help

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

26. ## Re: Excel Help

i used countifs.

27. ## Re: Excel Help

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.

28. ## Re: Excel Help

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

29. ## Re: Excel Help

someone help me!!!

30. ## Re: Excel Help

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
1999 - 2018