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

slyskipper95 said:can i use something other than those built-in designs in PowerPoint?

PowerPoint lets you build your own templates from scratch, too. Just go for it.

Can someone please give me a hand with this formula? I was working through an Excel guide from Egmont, specifically on page 34, trying to figure out how to get my final price to round a certain way. Basically, I want any result where the decimals are less than or equal to .50 to round down to the nearest whole number.$17, but if the decimal part is greater than .50, I want it to round up to the next integer.$33
The problem is, whenever I try to enter the specific formula provided in the book, Microsoft Excel keeps throwing an error at me, claiming it's invalid. Here is what the formula looks like:
=VALUE(INT((C5*1,1)*1,22)+IF(((C5*1,1)*1,22)-INT((C5*1,1)*1,22)<= .5, .05, .99))
I am almost certain there is a typo somewhere in the printed text, but I can't for the life of me figure out where they messed up.
To break it down for you, the 1.1 represents a 10% markup, and the 1.22 is the old sales tax rate.
What I am actually trying to achieve is adding the base price plus the markup and sales tax, then adding an extra $0.17 if the difference between that total and its rounded version is less than or equal to 0.5. Otherwise, I want to tack on an additional 0.99.
Man, what a complete mess this is!

Peter Hill4 said:=VALUE(INT((C5*1.1)*1.22)+IF(((C5*1.1)*1.22)-INT((C5*1.1)*1.22)

Look, you need to swap those symbols. For the div>

Yeah, if you look at books by American authors (like John Walkenbach and others), they use a comma (,) in their formulas instead of the semicolon (;) we're used to.
It actually tripped me up quite a bit back in the day too.

Is there a way to adjust the size of every single comment within a worksheet or an entire workbook all at once in Microsoft Excel 2003?
I’m dealing with a massive amount of comments here, and the default setting is just way too tiny. I really don't want to go through and manually resize every individual one by hand.

Well, I managed to figure out a fix...

http://www.contextures.com/xlcomments03.html

Hey everyone...

So, I have a bit of a question... I was wondering how one might go about creating a specific kind of chart in Microsoft Excel, perhaps using a pivot chart?

The idea is that we have two different values—let’s say, actual hours worked versus the maximum allowed hours.

I suppose what I am looking for is a way to have the chart display a light blue bar representing the actual hours worked, and then immediately following that, a dark blue segment showing just how much time is left until we hit that maximum limit.

I've included an example here, if you'd like to see...

It isn't an exact replica of what I'm envisioning, but I imagine the general concept is somewhat clear.


Thanks.

Hey everyone, I could use a little hand with something pretty basic tonight, assuming it's even possible within Microsoft Excel.

I'm trying to build a formula that identifies which quadrant a specific angle falls into. I attempted to tackle this using an IF statement, but clearly, I'm missing something because I just keep getting zeros everywhere...

Here’s the mess I've put together:

=if(0<angle_value<90;"Quadrant_I";"etc") => I suspect the issue lies right at the start; does the logic for 0<angle_value<90 actually work like that, or am I just being dense?

Hey there,

I could really use a hand with something here.

Here’s what my spreadsheet looks like:
Column A Column B Column C
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

So, what I'm looking for is a function that can sum up all the weights whenever the date and the ID match up. My Microsoft Excel file is massive—we're talking over 200,000 rows of data here—so it needs to be efficient. For example, if I look at ID 1949001 for the date 03/14/2008, the total should come out to 150.

Thx

cosmicbison19 said:quick question... how do I pull off a chart like this in Excel (maybe a pivot chart?) if I've got two values, like actual hours worked vs. max allowed hours?

You gotta use an extra column to calculate the gap between current time and the max limit.
Check out this link Time periods on charts

redcanyon5 said:Alright, look, I need a function that sums up all weights based on matching dates and codes. My Excel file is huge—we're talking over 200,000 rows here.

Say your table covers the range A1:C8

Put your criteria in cells E2 and F2

E2: date
F2: code

In cell G2, just drop this formula: =SUMPRODUCT((E2=$A$2:$A$8)*(F2=$B$2:$B$8)*($C$2:$C$8))

btw: check out this tutorial on using SUMPRODUCT for multiple criteria if you want to dig deeper

Dana Diaz8 said:Hey, I was hoping someone could help me out with something pretty basic tonight.

Give this a shot: Say you have numbers ranging from 0 to 360 typed into A2, then just pop this formula into cell A1:

=IF(AND(A2>0;A290;A2180;A2
There are probably more elegant ways to handle this, honestly.

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!

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. 😉

Is there any way to handle this using a formula or maybe a macro in Microsoft Excel 2003?

For instance,

I have columns C, D, E, F, and G hidden.

If I type the number 2 into cell A1... column C should automatically unhide.
Then, if I change A1 to the number 3... column D appears automatically.
And so on.

Maybe this will get you moving in the right direction:

So, here’s the deal. You’re going to use cell A1 as your little control switch—just type in a 2 or a 3 there. Then, you've got your data sitting in columns B and C, which you want to see show up over in column D. To make that happen, go ahead and pop this formula into D1: =IF($A$1=2;B🙂;IF($A$1=3;C:C;"")). Once you've done that, just grab the corner and drag it down to cover all the rows you have in B and C. Simple enough, right?

Basically, once you toggle that number in A1 to a 2, column D starts pulling everything from column B. It works just like that.

If you don't want all those messy source numbers cluttering up your view, you can just hide them.🙏 Just select the columns, head over to the menu, find Protection, and make sure you check the box next to Hidden, then hit OK.

Walter Jackson48 said:If you put either a 2 or a 3 in cell A1, and columns B and C contain the data you want to display in column D, here is how you handle it. In D1, enter this formula: =IF($A$1=2;B🙂;IF($A$1=3;C:C;"")). Then, just drag that formula down to cover all the rows used in columns B and C.

Yeah, that should do the trick... Thanks!

restlessheron19 said:Yeah, that might help... Thanks!

Look, what I was getting at regarding hiding those values—that was specifically about locking down the formula bar and protecting your data so nobody can just mess with it. If you actually want the numbers themselves to be invisible to the naked eye, there are two ways to go about it:
1. Just hide the columns entirely (go to Format, then Cells, then Columns, and hit Hide—you can always bring them back later with Unhide) and
2. You could select the actual numbers or expressions in the cells and just change the font color to white.
Hopefully, one of those does the trick for you.

(I was rushing through my last reply because I was pressed for time.)

Walter Jackson48
Yeah, I get where you're coming from... I see your point..
That example you gave isn't quite what I was looking for, though. It just shows the values. What I actually wanted was the ability to edit the cells being displayed. But, no matter—it was still helpful... if we can't find another way around it.

So, I've got this weird issue—whenever I open Word, the page looks tiny... how do I get it back to a normal size? It's an older doc, and usually, when I open stuff like this, it's totally fine. http://ch-slike.com/images/qGrb9.png

In Word 2007 (English), just select View. Under the Zoom section, you'll find Page Width (I assume in the American version you'd go to View)