Microsoft Office Q&A: General Discussion (Word, Excel, PowerPoint...)

I'm totally lost on how to add a conditional subtotal... 🤷

Tried this formula and it kind of works, I guess

SUMIF('Sheet1'!C2:C5;1;'Sheet1'!D2😁5)

Of course you didn't catch that I was talking nonsense! You can't really mess around with subtotals if all you want is a grand total on a separate sheet. But hey, you can solve this super elegantly using a Pivot Table, as long as you still have that Team column (you know, to show which group everyone belongs to)...
I can't give you a step-by-step guide because I'm working in OpenOffice, so my specific menu paths might not mean much to you, but here's the gist: select your data range (it's best if your first row has headers, so make sure to include those), go to the Data menu and pick Pivot Table (or whatever the equivalent is in Excel), set the destination to a new sheet in the options, then in the wizard, drag "Team" into the row labels and pull "Weight" into the values area. Boom. Done.
That’s how it works in OO, and I just tested it out myself—I whipped up a quick mockup with some dummy data in about 30 seconds flat..

Bryan Evans77 said:how do I make it so stuff on the first page doesn't mess with the second? Like, I want to lock it to a specific cell, but if I move that cell, I need the formulas to follow along too.

Just use a formula like this (and yeah, you gotta use absolute references for the range)
Copy the formula down

=SUMIF(Sheet1!$B$2:$B$5;Sheet2!A2;Sheet1!$C$2:$C$5 )
or
=SUMIF(Sheet1!$B$2:$B$5;A2;Sheet1!$C$2:$C$5)



Or just use a Pivot Table

Michael Jackson10 said:This is how you handle it in Excel—and I just tested it myself using some makeshift data—I managed to pull everything together (without any unnecessary fluff) in about 30 seconds...

A Pivot Table is frankly an "overpowered weapon" for a problem like this, though I suppose for a beginner, it might seem a bit... stubborn.

Smith's solution is actually what the person needs here—it provides a formula that they can actually grasp at any given moment, giving them total control over the situation. 🙂

You make a good point. Maybe I’m nitpicking a little, but if you ask me, about 70% of people out there don't even grasp the difference between absolute and relative cell references. And don't even get me started on what happens when they try to copy-paste them everywhere! Of course, I’m not saying that’s what happened here—it's just that the fact he tried using an Excel function instead of building some messy workaround with SUM() gives it away.
Plus, my biggest gripe is how OpenOffice syntax doesn't always play nice with Microsoft Office tools. Even I run into hiccups when switching between them. Sure, the free stuff covers maybe 95% of what I need daily, but man, sometimes I just facepalm at the Pivot Table results I get. I'm with you, though—it’s definitely not built for quick beginner fixes. For the heavy-duty stuff that you set up once and leave running forever? Not a chance. Besides, testing things thoroughly is just kind of in my DNA...

Thanks for the suggestions....

I'll see what actually works best for me and go from there..

Thanks 👍

Michael Jackson10 said:but if you ask me, about 70% of people don't even get the difference between absolute and relative references, let alone what happens when you start copying them,

Fine, we'll help 'em out. 😉
With relative references, the range shifts when you copy it, but with absolute ones, the range stays put. By "range," I mean the specific cell or span of cells you're working with.
If you want to turn a relative address into an absolute one, just toss in a $
sign.
Check this link if you actually care about ABSOLUTE vs. RELATIVE cell references in Excel

I'm looking for some insight on how to handle this specific formula in Excel...

Basically, I need to assume a value for KF and run the calculation—then, once I have a result, I take that resulting KF, plug it back into the formula, and recalculate. I need to keep doing this until the difference between my calculated solution and the initial KF used is within a 5% margin.

Is there an automated way to handle this kind of iteration, or am I stuck doing it manually?

Nathan Green76 said:Is there an out-of-the-box solution for this to run automatically, or does it have to be done manually?

You can automate it... just look up "iteration" on Google...

I’m also curious about the logic behind solving this...


So, when the lookup searches for 12, I end up with the first smaller result, but what I actually need is the FIRST larger one! Any idea how to get the right
one? EDIT:
Never mind, figured it out using INDEX and MATCH functions...

My local soccer club just put me in charge of tracking the league data... nothing too crazy, just the standings, match pairings, scores, and all that. I'm wondering if there's a specific software designed for this kind of thing, or if I should just stick to Microsoft Excel? I tried searching online for something dedicated, but I haven't had much luck finding the right tool yet.

When I try to solve this using an iteration, I keep getting an error—I honestly can't figure out why. You can see the specifics in the screenshot here.
To clarify, I am calculating this KF value, which is also part of the formula on the right side.

Just upload the Microsoft Excel file so I can give it a shot...

Nathan Green76 said:

Every time I try to solve this using iteration, I just get hit with an error. I honestly can't wrap my head around why—I've attached a screenshot so you can see the specifics.
Basically, I'm calculating this KF value which is also part of the formula on the right side.

Look, you probably need to start with an initial value (like 0, instead of what you've set up here) for k. If you don't, you're never going to reach a solution because you'll just end up in an infinite loop! What you've built here looks more like the second step (assuming you actually picked a starting point like k=0), but I'm not entirely sure if that's even correct given how you've programmed these formulas into Excel!

Here’s a quick example



The formula in cell A2 is (=B2), while B2 uses (=COS(A2))...

ruggedcyclist7 said:Just upload the Excel file so I can take a look.

File name: Kf problem.rar File size: 16.47 KB

Give it a look if you can...

Is the correct result 41880636992451.20 or 1000.842 if we're using MPa?

EDIT:
Just wanted to say, you set up the iteration in Excel perfectly... just double-check all your input units and the formula itself. The Excel part isn't the issue

just hit add/remove programs in the Control Panel and uninstall it? 🤷

Poooz!
I’m honestly not quite sure which subforum this belongs in—there are just way too many pages to sift through, so please bear with me if I’ve posted this in the wrong spot.

I’m running into a massive headache with Microsoft Outlook. Ever since I installed Windows 7 and updated to the new Office, my accounts and email addresses have all been imported and everything seems fine at first glance—except for one glaring issue: I am constantly getting duplicate messages. It looks like I have two separate folders in Outlook, and I can't for the life of me figure out how to detach or remove just one of them.

Since I had to manually enter my various email addresses—I have about ten of them for different reasons—a bunch of folders were created in the process. At least I managed to stop them from filling up with mail, but the problem remains: those two identical folders are sitting right there on the left side of my navigation pane.

I even tried setting up some rules for incoming mail, but that just seemed to make the duplication worse.

Does anyone happen to know how to kill this problem off for good? My apologies if my explanation is a bit muddled.

Thanks in advance for the help!

Arthur Ramirez20 said:My local soccer club put me in charge of tracking the league stats... just the basics, standings, matchups, scores... I'm wondering if there's a specific program for tracking all that, or if I should just stick to Excel? I tried searching online but didn't find much.

Maybe try building something similar to this for the NFL.