Posts by Benjamin Wilson7

Just a little more guidance:
Once you nest everything all together in the formula, that "NOT" function should only show up once at the very end—which I think you already figured out if you've gone through the Ivanov tutorial.🙂

p.s. Is it just me, or is this forum running painfully slow? Everything else on my computer is flying, but this...

ruggedcyclist7 said:Yeah, if he's got both numbers and names crammed into a single cell.
But if the numbers are in column A and the names are in column B, then flipping them into rows is a total breeze.

Is this supposed to be some kind of brain teaser or what?
Did our resident Expert finally get back from sick leave?

Just a quick side note:
Look, I wasn't really paying attention—I mean, I read it, but it totally slipped my mind (was looking at the previous page).
What actually "interests" me more is the Excel Expert who goes on medical leave and leaves behind such a messy pile of data.

Kenneth Carter27 said:So, here's the deal:

I've got this spreadsheet with a list of cities and their zip codes...
For example:
60601 Chicago, IL
90210 Beverly Hills
....
Now I need to split all those numbers and names (there are about 2,000 entries) so the zip code goes into one column (Column 1) and the city name goes right next to it (Column 2).
What's the easiest way to pull this off?
I tried using the Text to Columns tool, but it's giving me trouble because some places have multiple words or spaces, so it keeps splitting them into extra columns.

If your data is sitting in cells A1 through A2000, try this:
- In cell B1, type the formula: =LEFT(A1,5) and drag it down. This will grab those 5-digit zip codes.
- In cell C1, type the formula: =MID(A1,7,100) and drag it down. This pulls out the city names. (The assumption here is that there's just one space after the 5 digits before the name starts. If there isn't, just change that 7 to a 6. As for the "100," that's just the length of the name—it needs to be longer than the longest city name, so 100, 50, or even 40 works fine.)

This should fix almost everything you're looking at.
Once you're done, highlight Columns B and C, then go to Copy -> Paste Special -> Values. (That turns the formulas into actual text so they don't disappear).

By the way, if you want to solve that issue where city names get split into too many columns, you can always look into the CONCATENATE function. There's always more than one way to tackle a problem, which is exactly why I love Excel.

blackburn 1 said:I’m honestly losing my mind trying to find Excel 2007 online today. I’ve searched through a million tutorials and add-ons, but nowhere can I actually find the program itself. Has anyone successfully downloaded Excel from the web? Any specific sites you'd recommend? 🙂 thanks in advance

You might just lose your mind here too once you actually read the forum rules. Honestly, you're better off just picking up a copy at Best Buy 🙂—at least then you won't have to worry about anything breaking on your computer.

Kenneth Carter27 said:So, it’s my very first day on the job and I’m already hitting a wall with a few things:

I've got this spreadsheet where I need to figure out someone's gender (I have titles like Mrs. and Ms.)—how do I actually pull that off? I'm looking at about 2,000 entries here.

Talk about a double whammy, right?

Scott Brown36 said:Hey everyone! It's my first time posting on this forum. Here's why I'm here:
I've been using Office 2010, and until just a few days ago, Word was working perfectly fine. But apparently, I messed something up in the settings because now it's acting totally haywire whenever I try to type a document.
Every single time I hit the spacebar, instead of just getting a space, random letters (like p, m, etc.) pop up out of nowhere, and the order of the letters in the words I'm typing gets all scrambled up!
I'm positive I tripped some setting myself, but I honestly have no clue which one it was or how to turn it back off.🙂
Oh, and I'm running Windows 7.
Thanks! 😁

Try this:
Review -> Language -> Language Settings -> Proofing -> AutoCorrect Options -> AutoCorrect – uncheck "Replace text as you type."

Also check these:
1. Spelling and Grammar check -> turn it off
2. Auto Detect Language -> turn it on

You really ought to just turn off the "Check spelling as you type" option within the spelling and grammar settings.

Maybe just to be safe, you should also save it as a 97-2003 PPS file or something? I'm pretty sure Microsoft Office 2010 supports that, just like it does with the 2007 version.

It can't be empty if you want to split it into 32 slices, so go ahead and fill cells A1 through A32 with the same value—let's say 2.
Next, just insert a pie chart. Once it's there, right-click on the chart itself, head over to select data and highlight the range from A1 down to A32. Boom, there's your first series defined.
In the chart data range box, you should see =Sheet1!$A$1:$A$32
.
Now, you can change those values in A1-A32 whenever you want, but remember: if you want all those little slices to look identical, the numbers have to be the same.
And hey, feel free to stretch the chart out as big as you need.

p.s. You can actually use the chart to cover up column A, or just hide the column entirely.

You made my day... if you don't even know how to freeze a row in Excel.
Lately, you've been dropping question after question just to be difficult. Honestly, you're just harassing people here.

Since you're being so incredibly rude—let's be real, basic manners seem like science fiction to you—I had to get on your level. People like you only understand one language, right?

You're going on the blacklist, buddy. Forever.

Just highlight the entire row (or those specific cells from A3 to D3), head over to the VIEW tab, click on freeze panes, and grab the first option in the list. You can actually undo it right there too—it’s basically just a toggle switch.

John Cooper65 said:Hey everyone, if anyone is actually following this thread, could you please do me a huge favor? I really need some help figuring out how to tackle this assignment. Honestly, would someone mind walking me through it or maybe just helping me get it done?

Here’s how you do it: First, open up a brand-new Excel workbook on your desktop and save it as "exam." Once that's done, rename the first sheet to "assignment." Now, go ahead and write out the prompt, input your data table, fill everything in, and then lay out all your work—formulas and all. Simple enough, right?

How should we grade this? Here’s the breakdown: 0 to 5 gets you a 1. 6 to 7 lands at a 2. An 8 is a 3. A 9 earns a 4. And a 10? That's a solid 5.

So, c equals 5? Just like that? Is that really all we're working with here?

So...
Just pop those formulas into cells C4, D4, and E4. You’ve gotta know. I mean, this stuff is like Excel 101. It’s super basic. Instead of messing around with cell references, you can just type "5" directly into the formula. Sure, there’s that other way using the dollar signs to lock things down, but I don't want to overcomplicate things for you right now. Let's just keep it simple, okay?

So, you just pop the formula right into cell F3. Simple enough, right? Ever wondered how to handle a negative number when you're trying to find its square root? If you just run a standard formula, Excel is going to throw an error at you. It’s frustrating, right? The trick is to wrap your cell in the ABS function first. By using `=SQRT(ABS(C3))`, you're basically telling the program, "Hey, just look at the absolute value here." It strips away that pesky negative sign, turns the number positive, and then lets the square root calculation go off without a hitch. Simple, effective, and saves you from those annoying error messages! The term "anti-lock braking system" just works... I mean, there isn't really a better way to describe preventing that negative braking effect, right? 🙂
So, you just drop the formula right into cell G3. Simple enough, right? Ever wonder how to just grab the square root of a cell? It's actually pretty simple if you use this formula: `=SQRT(F3)`. Just pop that into Excel and you're good to go! Simple, right?
I wasn't really sure what was supposed to go in cell H3, so I just took a wild guess. Is that okay? Is there anything more satisfying than a quick little formula hack? Just `=F3&"©"`—simple, clean, and gets the job done. Sometimes you just need that tiny bit of automation to make things click, right?
Once you've finished typing all those formulas into cells C3 through H3, just highlight that whole range with your mouse. See that little black cross that pops up in the bottom right corner of cell H3? Just grab onto that and drag it down. Simple enough, right?
So, you just right-click on Sheet1, hit rename, and type in whatever you want. Simple enough, right? A task? Just one little task?
Once you're totally finished, just hit "Save As" and type in... An exam? Seriously? Just when you think you've finally caught your breath! And what about the storage location for the destination desktop? For instance, on my machine, the desktop sits right under the Favorites folder.

Come on, put a little more effort into this and just see it through to the end.
See ya!

If you’re looking to build a table of contents, here is how you do it:

First, go through the text you're working on and tag your categories using headings—like Heading 1, 2, 3, or 4. For example, your main titles would be Heading 1, while subheadings for different sections would be Heading 2, and so on, depending on the level of importance.
Then, just head over to the References tab and select "Table of Contents." If things change, you can always update it by right-clicking; you'll see two options there—one just to fix the page numbers and another to refresh the entire table.

I’d probably try reinstalling Microsoft Office, but if the issue was there from day one, I'm not even sure if that would actually fix anything.

frozenwolf3 said:Hey everyone.
I could really use some help if anyone knows how to fix this...
So, basically, Word keeps opening up in landscape mode with tiny margins. It’s like my settings just went haywire out of nowhere! Every single time I start a new document, I have to manually reset everything. Is there actually a way to set it to portrait with my preferred margins and save it so it stays that way every time I open the app?

You can actually make as many templates as you want! Just head over to the Microsoft Office button -> New -> My Templates -> Create New. From there, you can pick everything—margins, fonts, view, whatever you need. Just save it with a name like "Template 1."

The next time you open it, you'll find it right there where you started.

CTRL+PGDN moves you down (+END jumps straight to the end of the doc)
CTRL+PGUP moves you up (+HOME takes you back to the start)

If you're on a blank page, just use the mouse wheel or double-click.

p.s. Sending love to New York City!

=COMBIN(20,2)
The result is 190

Option two
First, go to your header row where you have DATE/CODE/WEIGHT and turn on filters—just black out the header row and head over to DATA->FILTER.

In the very last cell of column C, pop in this formula: =SUBTOTAL(9;$C$2:$C$8)

Now, use those filters! In the DATE column, pick the specific date you’re looking for (like 03/14/2008), uncheck everything else, and then in the CODE column, just leave the checkmark next to the specific code you need.
Once those filters are active, you'll see something similar to what's on the left.
The Subtotal function will automatically calculate the result based on whatever criteria you've filtered for.

And hey, you don't even need Excel 2007 for this.

If you format column "A" as a date, the filters give you endless ways to narrow things down—you can pick a specific day, a week, yesterday, before, after, a quarter... it's way more flexible than what was in Excel 2007, though I can't quite remember how it worked back in 2003.

So, there you go! You now have three different ways to get this done. 😉

redcanyon5 said:Hey there,

So, what I really need is a function that can sum up all the weights that share the exact same date and the same ID code. My Excel sheet is massive—we're talking over 200,000 rows here! So, for example, if I have ID 1949001 on March 14, 2008, I want the total result to come out to 150.
Thx

You could actually do it this way:

Here’s how Sheila would set it up:
A-DATE B-ID Code C-Weight
03/14/2008 1949001 25
03/14/2008 1949001 50
03/14/2008 1949001 75
03/14/2008 1949002 25
03/14/2008 1949002 50
03/15/2008 1949001 50
03/15/2008 1949001 50
03/14/2008 1949001 =SUMIFS($C$2:$C$8;$A$2:$A$8;A9;$B$2:$B$8;B9) =150
03/14/2008 1949002 =SUMIFS($C$2:$C$8;$A$2:$A$8;A10;$B$2:$B$8;B10) =75
03/15/2008 1949001 =SUMIFS($C$2:$C$8;$A$2:$A$8;A11;$B$2:$B$8;B11) =100

The formula goes into column "C". To use the =SUMIFS (which is the multi-criteria formula) , you'll need Excel 2007 in your toolkit.

Basically, A9, A10, A11 and B9, B10, B11 act as your criteria—the first one is the date, and the second is the ID.

A2:A8 is the range for the first condition (the date).
B2:B8 is the range for the second condition (the ID).
C2:C8 is the range containing the values you want to sum once both conditions (date and ID) are met.

Just a heads up: I've written this assuming you're going to drag the formula down from column "C," which is why those ranges need to be locked in place!