Determine the sum of sales for a particular day using one Excel formula
Thursday, January 29, 2009 11:31 AM
You can build an Excel formula that makes it easy to track sales. Here's how.
Microsoft Excel
Determine the sum of sales for a particular day using one Excel formula
Unlike printed forms, where users write directly on blank lines, typing data on a line in an online form only moves the line over to the right.
A single formula can enable your users to track daily sales totals. For example, say management wants to be able to determine the total sales receipts for each product category any day of the month.
Totals of daily product sales receipts are summarized in the worksheet shown in Figure A.
Figure A
Two sales figures are given for each date: one at the end of the night shift at 6:00 AM and one at the end of the day shift at 6:00 PM. When a manager enters a date in H2, we want to display the totals for each product in cells I5:I8.
Here are the steps:
- Select cells I5:I8.
- Enter the following formula:
=SUMIF($A$2:$A$21,$H$2,OFFSET($A$2:$A$21,0,MATCH(H5,$1:$1,)-1))
- Press Ctrl + Enter.
The worksheet in Figure B shows the sales results for October 5.
Figure B
Microsoft Word
Return to your last editing location when you open a Word document
One of the confounding things about Word is that when you reopen a document you've been working on, it puts you back at the top of the document. Unlike Excel, which takes you to the spot where you left off last time, Word's short-term memory always wants to start you off at the beginning again.
You can work around this if you press [Shift][F5] as soon as the document opens. [Shift][F5] is the Go Back shortcut, which cycles you between your four most recent edits during a Word session. But if you can remember to hit it immediately after opening a document, Word will jump to the last thing you changed before saving and closing that doc.
Automate it
If you always want Word to return you to the location of your last edit when you open a document, you can build a simple macro. First, go to Tools | Macro | Macros. In the Macro Name text box, enter AutoOpen. This is a built-in macro that Word will run automatically whenever it opens a document. (Like any macro, it will run only when it's saved with the document you're opening or when the document is attached to a template where you saved the macro. In most cases, Word will choose All Active Templates And Documents--making the macro globally available--but you may want to use the Macros In drop-down list to limit the macro's functionality to a specific document or template instead.)
Once you've entered the macro name, click Create to open the Visual Basic Editor. There, type
Application.GoBack
as shown in Figure A. When you finish, choose Close And Return To Microsoft Word (or press [Alt]Q).
Figure A
If you decide you're not crazy about this go-back behavior after all, just return to the Macros dialog box, select AutoOpen, and click Delete.
Microsoft Powerpoint
Use PowerPoint’s advanced animation to imply growth or movement
An easy way to bring a presentation to life is to imply growth or movement. Insert a few AutoShapes, perform a bit of animation magic, and a simple graphic takes on a life of its own. Your message will stick with the audience long after you shut down the system and turn off the lights--and that's what you're after.
Once you're familiar with the process, you'll find unlimited possibilities for adding a bit of life to your presentations.
An overview
The process for simulating growth or movement is simple. You string together a number of frames, similar to the process that creates cartoons. The graphic in each frame changes just enough to suggest growth or movement. Animation properties string the frames together, allowing the graphic to morph from one frame to the next.
The simplest animation can require as few as three or four frames. As a general rule, the slower the process, the more frames you need.
Suggesting movement
There are a number of ways to suggest change by using movement to make a point. You could help a flock of birds soar into the sky or send a rocket into space. We'll work with a much simpler example--we'll make a frowning face smile!
This example requires three frames: A frowning face, a neutral face, and a smiling face. You could add more to make the change smoother, but three is enough for our purposes and it keeps the example simple and easy to follow. To begin, use AutoShapes to create the frowning face. Specifically, use three circles from Basic Shapes and an arc from Lines. That's the first frame.
The second and third frames are even simpler. Off to the side (in the same slide) create a straight line and a second arc to fill the neutral frame and the upturned smile frame, respectively. Keep in mind that the size of all three smiles must be relatively the same. The easiest way to create the upturned smile is to copy the frowning smile and rotate it. That way, the smile is the same size as the frown. When creating the neutral frame, you'll just have to eyeball it.
Once you have created all three frames, as shown in Figure A, you're ready to animate them. Notice that the second and third frames contain only the parts that move. That won't always be the case. Sometimes, each frame contains a complete picture, but work with as few pieces as possible when you can.
Figure A
Use three frames to simulate turning a frown into a smile.
The animation settings determine how PowerPoint displays each frame. In this case, PowerPoint displays the frowning face as a whole picture. As the frown fades, PowerPoint fades to the neutral frame. As the neutral frame fades, PowerPoint displays the upturned smile frame.
Complete this animation as follows:
- Select the first frame, which in this case, is the frowning smile. (You can group the pieces that make up the face, but the only piece you'll animate is the frown.)
- Choose Custom Animation from the Slide Show menu.
- Click Add Effect, choose Exit, and then select Fade. There's no entrance property because we want PowerPoint to display the first frame and begin the animation as soon as it displays the slide.
- Choose After Previous from the Start drop-down list.
- Choose Very Fast from the Speed drop-down list.
- Select the second frame (the straight line).
- Click Add Effect, choose Entrance, and select Fade.
- Set the Start property to With Previous.
- Set the Speed property to Very Fast.
- Click Add Effect, choose Exit, and select Fade.
- Set the Start property to After Previous.Set the Speed property to Very Fast.
- Select the third frame, the upturned smile.
- Click Add Effect, choose Entrance, and select Fade.
- Set the Start property to With Previous.
- Set the Speed property to Very Fast.
Once you've animated each frame, stack them as shown in Figure B. That way, the three smiles fill the same space as the frames fade in and out. Now, save the presentation.
Figure B
Stack the frames so they seem to occupy the same space.
To preview the animated slide, press [F5]. The effect isn't visible online so be sure to download the demo presentation for a complete picture.
The first frame (the frown) doesn't have an Entrance effect, and the last frame (the smile) doesn't have an Exit effect. That means the frown is present from the beginning--it doesn't fade in.
Similarly, the smile doesn't fade. You can change both, if it suits your purposes.The fading entrance and exit properties and the speed between those fades simulate movement between the three faces. That movement creates mood. Make sure the mood fulfills or supports your message. You can alter the entrance and exit effects and speeds to customize the general mood of the animation.
The smiling face example uses a combination of With Previous and After Previous settings. There are three possible settings:
- On Click: You must click the slide to start the animation.
- With Previous: Animation begins as the previous item ends.
- With After: Animation begins immediately after the previous item is finished.
Remember, these properties determine how PowerPoint displays and subsequently hides each frame. The frown fades after its animation is complete. The neutral frame fades in as the frown fades out. Each frame repeats this structure.
You can change those effects, slow them down, speed them up, and even combine them. You could also move several pieces in each frame. For instance, you could change the shape of the eyes or even let the face wink.
The one problem with this particular example is that PowerPoint can't replicate a natural smile. That natural process is too smooth. You can add more arcs to make the process smoother, but you can't truly blend one frame into the next. You can only give the illusion of doing so. Don't try to imitate life, just allude to it. You're after the sentiment, not a realistic rendition.
Suggesting growth
First, you need the frames. The three hearts shown in Figure C increase in size. Use the instructions from the last example to apply the same animation scheme, except for the speed. Use Medium speed instead of Fast.
Figure C
Animating three different-size hearts suggests a growing and pulsing heart.
After applying the animation settings, stack the hearts in the center of the slide, as shown in Figure D. You can't tell it, but the two smaller hearts are under the largest heart. To view the animation, press [F5]. The timing is a bit slower this time, but the frames fade into one another as the previous example did. The slower fades seem to give the heart a pulse.
Figure D
Stack the three frames and watch the heart grow.
To life!
Liven up a presentation with a little movement or growth. At first, the process requires some experimentation, but the more you work with animation settings, the more intuitive they will become. Remember, anytime you engage the audience in a fun and meaningful way, you influence those viewers in a positive way.
There are currently no comments for this post.