As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
How you can use [EXCEL] to get womens* (*not guaranteed)
1ddqd
Registered User regular
Registered User regular
Old OP:I'm using & to combine the numbers of 2 cells (let's use A1 and B1)
Type 469549 in A1 and 1000 in B1.
Type the formula in C1. The formula is =A1&B1
The result is fine - the numbers combine. The problem I'm having is when I copy this formula through C357 (over 300 cells I need to combine the numbers from. It shows the formula progressing row by row (on C2, the formula is =A2&B2) but the result is the same result shown in C1, instead of progressing.
I'm using Office 2007, and I've tried copying just the values into a new book and starting fresh. Same thing. Any thoughts?
This program, Microsoft Excel can be the most valuable tool in an office. Where Access is too complex and multilayered, Excel provides solid reporting ground. If you instinctively open Excel when someone asks you to write something up (even if Word is a better choice) then this thread is for you!
1ddqd on
0
Posts
Goddamn it.
Although I cant complain, as its gotten me into a developer position from a really low starting entry as a contract servicer...
Had to turn in a set of walkie-talkies that I still had on me from our theater production to the professor. She had all of the student grades on an excel sheet, but was calculating the total points and grade percent by using the basic calculator built into windows and a piece of paper.
Five minutes later after showing her how to do autosum and find the average, her task that would have taken another ~3 hours was done.
Does that make me the excel guy?
http://steamcommunity.com/id/Cykstfc
Unfortunately, yes.
This is what I've become at work. Really, it's not about formulas, but about laying out the data on the sheet so that you can manipulate it...most optimumly?
What you will probably know/learn to absolutely blow peoples minds:
using =or(match()) to find an item on another list
using =vlookup (people think you are a fucking warlock after you do this)
If then statements
=vlookup([THE CELL YOU WANT TO FIND A MATCH FOR], [THE TABLE ARRAY YOU WANT TO LOOK IN (match must be in column 1)], [THE COLUMN NUMBER YOU WANT TO SHOW UP IN THIS CELL], [FALSE for exact match or TRUE for approx. match)
e.g.:
=VLOOKUP(A1,Sheet2!A1:H25,2,FALSE)
It will look in the first column of the range A1:H25 on sheet 2 for the value in A1 on the current sheet, then it will look at what is in column 2 (B in this case) of that row and return that value.
Other things that blow peoples' minds:
Using the RANK function to rank a list of values and combining that with an If/then to automatically have it mark duplicate ranks with 't' for 'tie'.
Seriously people think you're an excel ninja when you do this shit.
Don't even get me started on using Access to combine rows from an excel sheet.
EDIT: OK I will get me started on that:
I had a guy who had an excel spreadsheet that had values for the following:
Agency Name, Mode, Directly Operated/Purchased, Miles Traveled, etc.
There were probably 1000 rows in this sheet total. Every year he gets this data from another source and adds the DO/Purchased lines for each mode for each agency to make a new table, then he has to add them together for each agency for other tables he needs. It took him for fucking ever to do this.
I was like "you know, I could do that in 15 seconds with Access..." I was a god.
Conditional factors are fun too. Sumif() Countif()
I can't do menial work so I am always looking for ways to automate things. It makes me happy.
I redesigned some reports at work with SumIf and CountIf and then turned on Autofilter for the columns.
The VPs now know my name. Awesome.
Damn, add conditional formatting and you'll get a promotion*.
*Your mileage may vary. Requires Excel 2007. No refunds.
It's probably worthwhile to talk about some of the fucked up things people do with Excel. One of my previous bosses gave me a spreadsheet with a macro that she had recorded that was thousands and thousands of lines. It was basically building up intermediate values by inserting formulas into various cells, then using those values to build up more intermediate values, and using those to get the final values, and then the values where copied, and all the formulas deleted. All the formulas used relative positions, and many used VLookup (and I think HLookup. There's an HLookup right?). It was incredibly fucked up.
To this day I can't believe she though someone could edit the thing by hand. I took one look at it and replaced it with 100 lines of vba. It turned out the tables used as inputs came from a webapp, so I could have just screen scraped the webapp, and saved everyone a lot of trouble, but I just wanted to get rid of that thing.
About 8 years ago I worked in a call center and part of my job was to cut and paste a very large number of data from a phone call system into excel, sort the data and create tables which took friggin forever back in those days. After about 2 or 3 days of doing it manually I got the bright idea of making an excel macro for it (you can access other applications in excels macro functions most of the time). So, I automated this entire task. this macro would run for about 4 hours just collecting all the data and then churning it into usable chunks, formatting etc. I let this macro run everyday 5 days a week for about 2 months.
One day my computer at work crashed during the report generation and refused to reboot. I smelled smoke and opened the case. The computer had a 300 mhz celeron processor on one of those adapaters that made it perpendicular to the board, and I guess one of the clips had fallen off. Well the tech who had fixed the chip, used ducttape to stabilize the processor. This duct tape had finally gotten hot enough from that stupid processor running at 100% everyday for 2 months to finally melt through and lay on the board, melting all over the chip and everything.
So yeah, excel + stupid techs can kill computers.
For the longest time I had no SQL or .net software or access because of bureaucracy at work. I am not a programmer, so I cant have their tools, despite having a BS in computer science with a focus on programming. It took alot of time and results to change peoples minds.
I havent run into a position where Ive needed Hlookup yet, but I have everyone conditioned to set things up more like a database table. My old Director would refused to classify or mark data, instead insisting on color coding everything. I had to rearrange her data so often she finally got too frustrated with trying to fix everything with highlighting she got learn't. They are also all conditioned to request reporting in a manner that is close to speaking a SQL sentence "I want to see x, from all y, where z and zz". That is the holy grail and makes your life a ton easier becuase half the time they realize you cant link certain types of data uniquely, and retract other unreasonable requests halfway through making them.
Countif,sumif,search and Left(right()) and trim() combos rock, and make peoples minds asplode... My boss figured out the if statement, and then he saw a nested if statement and said "fuck this noise" and gave up ever since.
http://www.contextures.com/xlFunctions02.html
Need it for graphing various data.
If you have a tab-delimited text file you should be able to open it in excel pretty easily.
When you get the file into the program, mark it as delimited (not fixed width).
I got a lot of mileage out of those alone too. The conditional formatting just blew their minds, man!
When I heard this, I wrote a function for him in VBS(learning it as I went) in 4 hours. He was duly impressed.
haha in Excel 2007 it's much easier, but yeah, those things blew my mind in high school. It was like "I CAN DO AVERAGEIF?! Awesome!!"
I have a sheet that I'm using to calculate counts and sums based on values
that change daily.
I have "Days" (A1:A10) column that tells me how long an item has been
sitting idle.
I have "UPB" (B1:B10) field that tells me the value of that item.
What I'm trying to do is get a sum of Column B if Days (A) is greater than or equal to 180
and the UPB (B) is less than 50,000.
Something like that?
It may be easier to explain this in SQLish terms
I want to SUM C WHERE A >= 180 AND B <= 50000 - caps = code, not yelling
Then have your solution be a sumproduct of the two columns
=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000),C1:C10)
Egads, sorry, no I completely screwed this up. Edited original post to include the revision
Yes. I think SUMPRODUCT does what I want, but I don't know how to do the syntax properly.
You've got columns to spare on this sheet. It's not an unreasonable solution, and it exposes the logic of what you're doing to any third party that might have to interpret your work later on.
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000),C1:C10)
to
=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000),B1:B10)
Anyone ever see this before?
edit: figured it out. The file was saved as a real old version of excel. Save as -> current version, works fine now.
=SUMIFS(B3:B12, A3:A12, ">=180", B3:B12, "<50000")
=SUMIFS(column to sum, criteria range 1, criteria 1, criteria range 2, criteria 2)
There is no similar function for SUMPRODUCTIFS that I know of though.