Posts by Justin Torres77

How can I scale numbers by a specific percentage?
I really need a faster way to do this—manually typing everything row by row is killing my soul...

Here’s the deal:



That yellow column shows the percentage I want to apply to all the other numbers in the row.
My goal is to adjust each column by adding or subtracting that percentage. The tricky part is that some percentages are positive increases while others are negative decreases.

I’m dealing with a massive amount of data here, so if I try to do this by hand, I’m basically throwing my entire weekend away.

Perfect.

That's all I needed. Thanks.

Is there a way to change the font color for specific numbers in an Excel sheet—specifically, I want to target only the values that fall below, say, 200?

I already know the trick for highlighting negative numbers using the minus sign, but I'm a bit stuck on how to handle this particular range.

Thanks in advance!

Nicholas King said:If you're looking to pull data from a specific range—let’s say, for instance, A2 : A100—here is how that formula actually looks:

If you’re looking to run a calculation like `=SUMPRODUCT((A2:A100)*(0+MOD(ROW(A2:A100)...`, you're essentially diving into the deep end of Excel gymnastics. It’s one of those formulas that looks like a cat walked across your keyboard, but there's actually a method to the madness—usually involving some kind of alternating pattern or row-based logic. I see people trying to pull off these "clever" little tricks all the time, thinking they've cracked a secret code, when really they're just making their spreadsheets a nightmare to audit six months later. Using `MOD` and `ROW` inside a `SUMPRODUCT` is basically the spreadsheet equivalent of building a house out of toothpicks—it might look cool while you're doing it, but don't be surprised when the whole thing collapses the moment someone else tries to touch it. If you're trying to sum every other row or apply a specific weight based on where the data sits, this works—but let's be honest, most of the time there's a much cleaner way to handle this without turning your workbook into a cryptic puzzle. Just a thought.-1;7)=0))

Look, this bolded unit is actually pretty critical—you’ve got to adjust it based on which row your data actually starts on. Basically, you're just taking the starting row number and subtracting one (for example... If your data starts sitting pretty on row ten, just go ahead and shift everything up by nine—set that offset to -9 and call it a day.).

Can't see the forest for the trees. 🙈

Justin Torres77 said:So, if you take column 3 and run it through that formula Nicholas King was using—you know, the one where he’s basically playing wizard with the cells—it looks like this: =SUMPRODUCT((E3:E66)*(0+MOD(ROW(E3:E66)...-4;7)=0))

And why, exactly—why -4????😕

Look, I didn't exactly invent this formula—it's not like I'm some kind of mathematical genius or anything. 🙂

Your "formula"—man, you’re just a total joke. 😁

Kyle Johnson2 said:I’m posting this for the sake of everyone else reading this thread—you explained things pretty well just now, so I don't see what's wrong with the formula @LordByron provided
=SUMPRODUCT((A2:A100)*(0+MOD(ROW(A2:A100)-1;7)=0))
Obviously, if you start from A3, you’ll need to adjust it to -2 in the formula

That version you shared here
=SUMPRODUCT(--(MOD(ROW(A3:A465)-ROW(A3);7)=0);A3:A465)
doesn't quite hit the mark in this specific case (at least based on my understanding of what you're trying to achieve)—so, for the benefit of anyone else lurking who might run into a similar headache, could you walk us through your logic? Maybe include a screenshot if it makes it easier

Sorry for the radio silence earlier—just been buried under a mountain of stuff lately.

To test this out, I copied a bunch of identical numbers into different columns and trimmed down the dataset a bit. My actual sheet has over 700 rows, though

Column 1: Summed up using the "old school" manual way—3+10+17+24+31, etc.
Column 2: Summed using "my" formula, which yields the same result: =SUMPRODUCT((D3: D66)*(0+MOD(ROW(D3: D66)-3;7)=0))
Column 3: Summed using LordByron's formula: =SUMPRODUCT((E3: E66)*(0+MOD(ROW(E3: E66)-4;7)=0))

So, I did a little digging online—mostly trying to follow Lord Byron's logic—to see how our version should look, and according to my math, it should give me the exact result I'm looking for.

=SUMPRODUCT(--(MOD(ROW(A3:A465)-ROW(A3);7)=0);A3:A465)

But it’s still coming up slightly different from this...

Nicholas King said:For data sitting in, say, range A2 : A100, you'd use this:

=SUMPRODUCT((A2:A100)*(0+MOD(ROW(A2:A100)-1;7)=0))

Thanks for the help, 👍

Nicholas King said:I mean, if it shows up every seventh time, he can grab it—that’s enough for him. He doesn't need anything else. 🙂

Here I have 6 products and the total for every single retail location.

Basically, each location has 7 identical rows (same product names, just at different spots).

What I actually need is a sum at the bottom for all the 1st items, all the 2nd items, and so on...

Nicholas King said:If you're looking at data sitting in, say, the range A2 : A100, here’s how you’d write that formula:

=SUMPRODUCT((A2:A100)*(0+MOD(ROW(A2:A100)-1;7)=0))

That little bolded number there is the key—you'll need to tweak it depending on where your actual data starts. Basically, take your starting row number and subtract one (so, if your data kicks off on row 10, you'd swap that out for -9).

I just gave this a shot and I'm getting a totally wrong total 🤷

Quick Excel Question

I’ve got this spreadsheet sitting here with a few hundred rows...

It involves various product categories spread across different locations—not that the specifics actually matter right now. 😁

What I need to do is sum up every 7th row within a single column at the bottom.

Any ideas on how to pull that off?

My paperwork says I have the option to swap this out for community service hours...
So that’s exactly what I’m going to do—we'll see how that plays out. 😁

So, I just got the paperwork back for a traffic violation, and the fine is sitting at $167.
In the past, I used to just casually swap out those kinds of penalties for community service hours—it was easy, and honestly, nobody ever actually made me show up to do the work because it was basically just a formality.
Now I'm wondering if things have actually changed under the Traffic Safety Act—is this community service thing a real thing now, or is it still just something they write down on paper to look good?

boilsoft videosplitter

So, what are you seeing at the bottom of uTorrent—is it a green checkmark, a yellow triangle, or, God forbid, a red circle?
If it's showing up red or yellow, you’re going to need to open a port on your router. Check this out here for some instructions on how to handle port forwarding. Just find your specific device model and follow the steps.