Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

Alexander James77 · 2012-03-17T08:48:56+00:00

Walter Jackson48 said: Perhaps this specific formula might prove useful to you: =OFFSET(COLONA;COLUMN()-COLUMN($E$1)+((ROW()-ROW($E$1))*(ROWS(COLONA)/1));0;1;1…

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#861 · Jun 9, 2013

rapidhawk23 said:Whoops!! Link's crawling with viruses!!

Not sure where you're getting those viruses from—give me some details if you can. Is this just some free file-sharing site?
What browser are you using? What else?
My AVG and Malwarebytes aren't flagging anything.
Here’s the link back on that server
If you don't want to deal with that, I put it on my own server. It'll stay up for a few days before I scrap it. Download link here

Hope you figure it out. Maybe go brush up on some Excel basics or something.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#862 · Jun 9, 2013

Jeffrey Moore4 said:Is there actually a way to just type this stuff—name, address, whatever—once and have it automatically update everywhere else in my files? I need it to sync across all the different spots in the text without me doing the manual grunt work.

Maybe just run a macro to pull the data straight from a TXT file.
Or just go through. Autocorrect settings. Either use shortcuts or just go through Autotext. Or both. Whatever. automation for repetitive tasks
Same in Excel.

Ryan Clark5 Newcomer

4 messages
joined Mar 2013

#863 · Jun 12, 2013

Greetings 🙂, does anyone know if there have been any advanced Excel courses held in Washington, D.C., perhaps during the summer, lasting around 20 hours or so? I am looking to refresh my skills while I have some free time 😁

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#864 · Jun 13, 2013

Ryan Clark5 said:some advanced Excel course in Washington, D.C.

What even counts as an "advanced Excel course" to you? Like, what specific field or actual skills are we talking about here?

Peter Sanchez15 Member

10 messages
joined Jun 2013

#865 · Jun 13, 2013

Hey everyone, I was wondering if someone could help me out with a little logic puzzle I'm stuck on here:

So, I’ve been using this specific formula to pull certain results, and then I go ahead and round those numbers to two decimal places visually, but when I try to run a sum, my Excel keeps pulling in all those tiny, invisible digits behind the scenes instead of just what I actually see on the screen. Does anyone know how I can make Excel actually add up just those two decimals and ignore everything else?

Thanks a bunch, you guys are the best.

hiddenotter98 Member

27 messages
joined Mar 2012

#866 · Jun 13, 2013

Peter Sanchez15 said:Could someone please help me troubleshoot this:

I'm using a formula to get a specific result which I then round to two decimal places—but how do I make Excel actually sum those two decimals instead of the full, unrounded values?

Thanks everyone

Like this

Peter Sanchez15 Member

10 messages
joined Jun 2013

#867 · Jun 13, 2013

hiddenotter98 said:Look,

I’ve actually already tried that approach, but I just can't seem to wrap my head around how to make that ROUND function play nice with the formula I’m using?
The thing is, when I click into the cells I'm trying to sum up, I don't see the final number, I just see the underlying formula used to calculate it, which is making things a bit messy.

Michael Jackson10 Active Member

140 messages
joined Feb 2023

#868 · Jun 14, 2013

Peter Sanchez15 said:I already tried that, but I just can't figure out how to wrap the ROUND function around my existing formula?
Because when I look at the cells I'm adding up, they don't show the actual number, they just show the formula used to get that value.

Basically, instead of plugging in a single number, you just take the entire contents of that cell—the whole formula—and drop it into the ROUND function:

=ROUND(formula,2)

For example:
=ROUND(SQRT(1245/45,25*0.05-2.25),2)

Just "wrap" your whole thing in the ROUND function, and it’ll tidy up that final result to two decimal places for you...

Walter Jackson48 Active Member

234 messages
joined Feb 2010

#869 · Jun 14, 2013

Maybe this might actually be useful for you guys:

Let’s say we take cell A1 and plug in a decimal like 123.4562, then over in B1, we toss in 12.9876
(Once you round those figures down to just two decimal places, you end up with: 123,46 and 12,99). Now, if you’re under the impression that you need to sum up those specific bolded digits (so, 46 + 99 = 145), go ahead and try putting this formula into C1: =RIGHT(ROUND(A1,2),2)+RIGHT(ROUND(B1,2),2) . I mean, sure, the formula could probably be streamlined or whatever, but I'm just trying to point you toward the logic of solving the actual problem here. If that does the trick, then great.

Ryan Clark5 Newcomer

4 messages
joined Mar 2013

#870 · Jun 14, 2013

Kyle Johnson2 said:What specifically do you mean by an "advanced Excel course"? Which specific field, set of actions, or area of expertise are you referring to?

I found this one
, and it is quite affordable for students 🙂

Nicholas King Regular

716 messages
joined Jan 2023

#871 · Jun 14, 2013

Peter Sanchez15 said:I'm using a formula to get certain results which I then round to two decimal places—but how on earth do I make Excel actually sum up those two decimals instead of the full underlying numbers?

Providing a concrete example of what you're trying to achieve would be about as precious as a cold bottle of water in the middle of the Mojave Desert🙂.

Alex Barnes4 Newcomer

6 messages
joined Dec 2010

#872 · Jun 19, 2013

I’m running into a bit of a snag in Excel... so, let's say I have a column from A1 to A10 where I'm just plugging in some natural numbers, and then I've got another column from B1 to B10 filled with different natural numbers... now, what I’m really trying to do is get cell C1 to show me the total count of how many times the value in column A actually exceeds the value in column B... basically, I want the spreadsheet to look at A1 against B1, then A2 against B2, all the way down to A10 against B10, and every single time that number in column A happens to be larger than its counterpart in column B, I want cell C1 to increment by +1... would anyone be kind enough to write me a little function that handles this specific scenario? Once I have that working, I should be able to tweak it quite easily to fit whatever my actual project requires
...

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#873 · Jun 19, 2013

Alex Barnes4 said:in cell c1, I'm adding +1. can someone write me a function that does this exact thing (so I can just tweak it for my actual project later)?

You didn't specify what exactly you're adding that "1" to, or what happens if the number in A is actually smaller than the one in B.

Just use the IF function.
This formula in cell C1 will do this:

If the value in A1 is greater than the value in B1, then add 1 to A1 and show that result. If not, leave the cell blank.
Code:

=IF(A1>B1,A1+1,"")

Basically, the IF function/formula takes three arguments: =IF(condition; result if true; result if false)

frozenheron7 Member

26 messages
joined Nov 2020

#874 · Jun 19, 2013

Alex Barnes4 said:I'm running into a bit of a headache in Excel. Let's say I have a column from A1 to A10 where I'm inputting whole numbers, and then another column from B1 to B10 filled with different whole numbers. Now, I want cell C1 to show me the total number of instances where the value in column A is greater than the corresponding value in column B. Essentially, I need to compare A1 to B1, A2 to B2, A3 to B3... all the way down to A10 vs B10. Every single time the number in column A beats the one in column B, I want C1 to increment by +1. Can anyone write me a function that handles this specific logic? Once I see how it works, I can easily tweak it for my actual project.

Look, there's a bit of a workaround here. I'll have to sit down and figure out the exact syntax to make this work with a COUNTIF function.

To get started, just pop this formula into cell C1:
=IF(A1>B1,1,"")
then grab the fill handle and drag it down through all 10 rows.

This little formula will basically flag every instance where A is greater than B by putting a "1" in column C.
Once you've done that, just sum up everything in that C column:
=SUM(C1:C10)

Alex Barnes4 Newcomer

6 messages
joined Dec 2010

#875 · Jun 19, 2013

frozenheron7 It seems like you might have missed pasting the actual content you wanted me to rewrite... I was sitting here ready to dive in and get to work, but there isn't any text for me to transform yet... If you drop the original message in, I'll get started on turning it into something fresh and uniquely American for you... just let me know whenever you're ready...
I was just thinking about a bit of a workaround here, though I'll probably need to sit down and really dig into how to structure that logic using a COUNTIF function...

To get things moving, you'll want to start by dropping this formula right into cell C1...
So, I was sitting there staring at my screen this morning, just thinking about how much simpler life would be if we didn't have to deal with all these complex formulas when a basic logical check would do... you know? I was looking at a spreadsheet where I needed to compare two values, specifically checking if the value in cell A1 is greater than whatever is sitting in B1, and if that condition is met, I just wanted it to spit out a "1"... otherwise, I wanted it to stay completely blank so the sheet looks clean and doesn't get cluttered with zeros everywhere... it’s funny how those little things can make such a huge difference in how readable a report feels... anyway, the formula you're looking for is just `=IF(A1>B1,1,"")`... it’s one of those small, elegant little tricks that keeps everything tidy... turns out, sometimes less really is more...
And then you just drag that down across all ten rows...

So, if you're looking to set this up, the function will basically just go through and drop a number 1 into column C whenever that specific situation pops up where a happens to be greater than b... it's a pretty straightforward way to track those instances as they occur...
And then, once you've got everything else sorted out, you just want to wrap things up by totaling everything sitting there in column C...
If you're just looking to add up a quick column of numbers without making things complicated, you can always just use the standard formula... something like =SUM(C1:C10)... which works perfectly fine for most everyday tasks around the office... though I suppose if you're dealing with massive datasets or anything more complex than a basic grocery list, there might be a few different ways to approach it depending on how much manual labor you want to put in... but for now, that should get the job done quite nicely...

Yeah, I hear you, that part is actually pretty straightforward, but my real question was more about how to get that total calculated without having to stretch out column "C" or mess with the layout at all... I just want the sum to show up in one single cell and nothing else...

frozenheron7 Member

26 messages
joined Nov 2020

#876 · Jun 19, 2013

Alex Barnes4 said:Yeah, sure, that’s easy enough, but my actual question was how to calculate that sum without having to stretch out column "c"—basically, I just want the total to show up in one single cell and nothing else.

Look, I went ahead and whipped up a little Macro for you, just because it’s easier for me to explain it this way. 🙂
First, go ahead and enable the Developer Tab (File -> Customize ribbon -> Main Tabs -> check the Developer tab)

On the Developer Tab, go to Insert -> button and draw yourself a button on the sheet.
When the window pops up asking about a macro, just click New
and paste this code right between the Sub and end Sub lines:

Dim counter As Integer
For counter = 1 To 10
If (Worksheets("Sheet1").Cells(counter, 1).Value > Worksheets("Sheet1").Cells(counter, 2).Value) Then
Worksheets("Sheet1").Cells(1, 3).Value = Worksheets("Sheet1").Cells(1, 3).Value + 1
End If
Next counter

Once you click that button, it’ll perform exactly what you’re looking for.
If you need to run it over a larger range, just look at the line "For counter = 1 To 10",
swap out the number 10 for whatever number you actually need,

Let me know if it works for you.

Kyle Johnson2 Newcomer

1 message
joined Feb 2015

#877 · Jun 20, 2013

Alex Barnes4 said:but what I was actually asking was how to get that sum without expanding column "c"—basically just getting the total in one single cell and leaving everything else alone.

Yeah, totally missed the point of the first question. 😉My bad.

Try using the SUMPRODUCT function.
Code:

=SUMPRODUCT(1*(A1:A10>B1:B10))

Or just use SUM paired with an IF function, but you gotta hit it as an array formula
Code:

=SUM(IF(A1:A10>B1:B10,1,0))

Alex Barnes4 Newcomer

6 messages
joined Dec 2010

#878 · Jun 21, 2013

Kyle Johnson2 said:To be honest, I don't think I quite caught the drift of that first question at first... it kind of went right over my head... 😉

Maybe you could try using a function instead... it might make things a bit smoother... I’ve been spending quite a bit of time lately just staring at my screen, trying to wrap my head around how certain functions behave when you really start pushing them... there’s this one specifically, SUMPRODUCT, that always feels like it has more layers than people realize at first glance... most folks think it’s just a quick way to multiply arrays and then add them up, which isn't wrong, I suppose, but once you start getting into those complex conditional logic scenarios, it turns into something else entirely... it’s almost like a Swiss Army knife that you didn't realize was actually a full workshop... anyway, I was thinking about how much cleaner things look when you stop relying on massive chains of nested IF statements and just let the math do the heavy lifting for you... it’s a bit of a rabbit hole, honestly, but a very rewarding one if you have the patience to sit with it...
Here is the code:
So, I was sitting there staring at my screen this morning, just nursing a lukewarm coffee and thinking about how we often try to make things more complicated than they actually need to be when we're working away in Excel... you know that feeling? Anyway, I came across this little formula snippet today, `=SUMPRODUCT(1*(A1:A10>B1:B10))`, and it really got me thinking about those clever little logical shortcuts we use to bypass the usual grind... it’s essentially a way to count how many times the values in one range exceed the values in another, using that SUMPRODUCT trick to turn those True/False results into ones and zeros so they can actually be tallied up... it's quite an elegant little workaround for anyone trying to run quick comparisons without jumping through all the hoops of a complex array formula or setting up extra helper columns... just a quiet, efficient way to get the answer you need while you're deep in the zone...

Ah, yeah... that's exactly what I was looking for... 🙏Thanks so much, you really are a lifesaver... 👍

Benjamin Long7 Newcomer

2 messages
joined Jun 2013

#879 · Jun 28, 2013

Greetings, I am seeking assistance with developing an Excel Macro.

My goal is to take the content from a specific cell and input it directly into the Find What field.

Currently, my script is stuck; it simply repeats the exact value that was present when I recorded the Macro.

What I actually need is the flexibility to change the cell's value manually and then run the Macro to search for that updated information.

I hope my explanation is clear. 🙂

Thank you in advance. 🙂

electricdriver93 Member

24 messages
joined Aug 2007

#880 · Jun 28, 2013

Benjamin Long7 said:Hi, I was hoping someone could help me write an Excel Macro.

I need to take the content from a specific cell and plug it directly into the "Find What" field.

The issue I'm running into is that whenever I run my current macro, it just uses the exact value that was sitting in the cell at the moment I recorded the macro.

What I actually need is the ability to change the value in that cell manually, and then have the macro search for whatever new content I've entered.

I hope that makes sense. 🙂

Thanks in advance. 🙂

Hmm, you aren't being entirely clear.
In any case, let's assume you want the term you're searching for to be pulled from cell A1, and the term you're replacing it with to come from cell A2.
You simply open the editor, define your variables, set the replacement range, and you're done.

For example:

Sub Macro1()

Dim trazi, zamjeni As Variant
trazi= ActiveSheet.Range("a1").Value
zamijeni = ActiveSheet.Range("a2").Value
'let's say you are replacing values on a different sheet
Sheets(2).Cells.Replace What:=trazi, Replacement:=zamjeni
'if you are replacing within a specific range on the active sheet (e.g., b1:b10), then:
ActiveSheet.Range("b1:b10").Replace What:=trazi, Replacement:=zamijeni

'
end Sub Hopa

Hope it helps.

Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

🔗 Similar threads