Data’s got a brand new bag: PowerPivot for Excel (video)

I liked the heads up but was expecting a little more from the video. For instance a quick example how you linked up your sheets in the way you showed.

Still, thanks for the heads up but my data amounts are hardly enough to expand on it like this. But having said that, a small side-step if I may...  Since beginning this week I started using Outlook 2010 (was using SeaMonkey's mail). Part of this decision was triggered by the 'Business Contact Management' extension; that critter is outrageous for small businesses like I represent.

I'm slowly, but steadily, starting to become -very- impressed with the way you can inter-exchange data between Office applications. A bit comparable like you showed here; one sheet easily connected to the other. In my case; accessing Outlook data (contacts) from Word (using VBA obviously).

Alas, thanks for sharing but..  - no offense - but I am looking forward to one of your "casual" example video's. Those really rock, I'm even actively (carefully) spreading them with some of my customers :-)

View the original article here

Remembering Nathan (Nate) Oliver

thank you for your tribute to Nate

I am so sad that I will not see Nate or talk to him again.  He was gifted in many ways.  His brilliant mind and physical skills were tempered by his passion for people and being helpful.  I love what everybody has written, even those who are just starting to learn who Nate was through his legacy of posts, blogs, pictures, and more.  I took many pictures of Nate over the years.  They are here:

skydrive.live.com

Goodbye my friend and colleague.  We had great times and I always learned something from you.  Even though you are gone, your code lives on,  thank you

To Nate's family and friends, I give my most sincere condolences

Warm Regards,

Crystal

Microsoft MVP, Access

www.accessmvp.com/strive4peace

View the original article here

Tracking small projects in Excel

Hi Anneliese,

If you ask any project manager what's the worst thing that he can do to manage his project's, he'll tell you "using Excel to schedule and manage the project".

MS Project is way better at that and Excel is really not made to manage projects. Problems with Excel will start one you have change requests, how can you handle them?

I'm a contributor to PM Hut and one of the common things that project managers tell us is how Excel "ruined" the project (of course, you might think that they're blaming the tool, but when you start hearing the same thing over and over again, you will start wondering).

PS: Glenna's work is perfect, and I'm not criticizing it in any way (as she tried her best to make Excel work as a PM software), but again, and in my opinion (and the opinion of many other project managers), Excel is just not made for project management.

View the original article here

Introduction to Spreadsheet Risk Management

This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit

Part 1: An Introduction to managing spreadsheet riskPart 2: How companies can manage their spreadsheet riskPart 3: Excel’s auditing functionsPart 4: Using external software packages to manage your spreadsheet risk

The potential impact of spreadsheet error hit the UK business news recently after a mistake in a spreadsheet resulted in outsourcing specialist Mouchel issuing a major profits warning and sparked the resignation of its chief executive.

See the full news article here: http://www.express.co.uk/posts/view/276053/Mouchel-profits-blow

Over the next few weeks we will look at the risk spreadsheets can introduce to an organisation and the steps that can be taken to minimise this risk.

Because we all love Excel, right? True certainly, but the main reason is that it is intuitive, flexible, cost effective and provides quick solutions to high priority day to day problems.

And what is the alternative? The IT department. The simple fact is that end user developed spreadsheets often fill the gap between the current business requirements and formally managed IT systems.

Unfortunately this reliance on spreadsheets, rather than robust, well governed IT solutions can add significant risk to an organisation if it is not properly managed.

Spreadsheet risk is the risk that a business could lose revenue and profit, fail to comply with regulators or find its reputation damaged as a result of spreadsheet error (be it fraudulent or unintentional).

Poorly structured spreadsheets can also lead to a loss of productivity and increased audit costs, further damaging the bottom line.

A recent study of typical enterprise spreadsheets by the Tuck School of Business at Dartmouth found that 94% of spreadsheets and 5% of all formulae within spreadsheets contain errors.

The European Spreadsheet Risk Interest Group (EuSPrIG) are the voice of best practice spreadsheet development and the management of spreadsheet risk.

Below are a couple of examples of what can go wrong from the EuSPrIG website:

C&C Group admit to mistake in revenue results

Shares in C&C fell 15 per cent after it said total revenue in the four months to end-June had not risen 3 per cent as reported, but had dropped 5 per cent. C&C said cider revenues in the UK had fallen 12 per cent, not 1 per cent, while cider revenues in Ireland were flat instead of up 7 per cent as reported last week.

C&C’s group finance director and COO said the error in last week’s announcement occurred after data were incorrectly transferred from an accounting system used for internal guidance to a spreadsheet used to produce the trading statement. “It was basically human error… there’s nothing wrong with our accounting systems,”

FSA fines Credit Suisse £5.6m

The FSA decided to impose a financial penalty of £5.6 million on the UK operations of Credit Suisse in respect of a breach of Principles 2 and 3 of the FSA’s Principles for Business:

Principle 2 states that “A firm must conduct its business with due skill, care and diligence.”Whilst Principle 3 requires that “A firm must take reasonable care to organise and control its affairs responsibly and effectively, with adequate risk management systems.”

More specifically, section “2.33.3. The booking structure relied upon by the UK operations of Credit Suisse for the CDO trading business was complex and overly reliant on large spreadsheets with multiple entries. This resulted in a lack of transparency and inhibited the effective supervision, risk management and control of the SCG {Structured Credit Group}”

Excel is, and is likely to remain, the first choice for businesses when developing financial models and analysing data. The risk that this introduces to businesses if unmanaged is real and potentially material.

In the next article we will look at ways that companies can manage their spreadsheet risk.

In my brief usage of Excel, I have experienced several risky situations. Sometimes it just a mild data loss, other times, there was a potential of revenue loss or customer annoyance. Due to the economic slowdown many large and small corporations are employing spreadsheet based solutions. And if you do not understand the risk & manage it, then your risk being featured on EuSPrIG’s horror stories page.

Do you know (or experienced) a spreadsheet horror story? Please share your ideas and best practices with us using comments.

Many thanks to Myles for writing this series. Your experience in this area is invaluable. I am really keen to learn about the best practices and adopt them in my business. If you enjoy this series, drop a note of thanks to Myles thru comments. You can also reach him at Excel Audit or his linkedin profile.

RSS feed for comments on this post. TrackBack URI

View the original article here

Formula Forensics No. 005 – Zebras and Checker-Boards

This week in Formula Forensics we’ll look at, Zebra Stripes and Checker-board Conditional Formatting.

This idea is inspired by a number of posts over the past few years asking about zebra stripes but specifically BobR who in in June 2011, also asked about Checkerboards in the post: Want to be an excel conditional-formatting Rock Star, Comment No. 154.

I got the conditional format for alternating row and column colors,

Is there a conditional format to make it a checkerboard whereas the cell A2 will remove either the conditional for the row or column and then alternately to A4, B1, B3 etc?

Chandoo responded fairly quickly with this Conditional Formatting formula:

=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)

Unbeknownst to Chandoo I posted this about a minute later:

=ISODD(ROW()+COLUMN())

Both formula correctly answer BobR’s question.

So today we’re going to pull apart Zebra Stripes and Checker Boards and see what makes them tick.

As always you can follow along in a download file here: Download File.

Zebra Stripes as Conditional Formatting is simply applied using a simple formula within Conditional Formatting.

=MOD(ROW(),2)=0

Conditional Formatting requires a formula that returns a boolean “True” to apply a format or a Boolean “False” to not Apply a format.

So the formula is better read as: If MOD(ROW(),2)=0

And  If MOD(ROW(),2)=0, the formula will evaluate as True

This is best evaluated as 3 columns on a worksheet.

In cells

B5:B10 The formula =Row() returns the Row Number

C5:C10 The formula =Mod(Row() ,2) returns the Mod of Row Number, divided by 2

The Mod function returns the remainder of the division of the Row Number divided by 2,

So in Row 5, Mod(Row(),2) = Mod(5, 2) = 5/2 = 2 Remainder 1 = 1

and in Row 6, Mod(Row(),2) = Mod(6, 2) = 6/2 = 3 Remainder 0 = 0

D5:D10 The formula =Mod(Row() ,2)=0 checks the remainder against the value 0

This is what evaluates to either True or False depending on the Row number.

Where the Values are True the Format will be applied (Even Rows)

The Conditional Formatting can be applied to Odd Rows If the Formula is slightly altered

=Mod(Row() ,2)=1

Similarly the formatting can be applied to Columns using

=MOD(COLUMN(),2)=0/1

RobR received two responses to his Checker-Board Conditional Formatting request.

=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)

and

=ISODD(ROW()+COLUMN())

Lest see what’s inside these two formula.

This is a simple If Formula with 3 components

=IF(MOD(ROW(),2)=1,MOD((ROW()-1)*8+COLUMN(),2)=0,MOD((ROW()-1)*8+COLUMN(),2)=1)

If Condition        MOD(ROW(),2)=1

Value if True:     MOD((ROW()-1)*8+COLUMN(),2)=0

Value if False:    MOD((ROW()-1)*8+COLUMN(),2)=1

The If Condition is already known to us, as it’s the same formula used in the Zebra Stripes above.

It evaluates to True when it is on an Odd Row.

So when it is an Odd numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=0

And when it is an Even numbered Row Excel will look at MOD((ROW()-1)*8+COLUMN(),2)=1

We can notice that these are the same formulas which have a different ending of =0 and =1

MOD((ROW()-1)*8+COLUMN(),2)=0

This section Takes each Row subtracts 1 and then multiplies this number by 8. This can be expressed as simply as saying multiply the Row * 8.

This will always return an Even Number and could have been simplified to Row()*2

MOD((ROW()-1)*8+COLUMN(),2)=0

The next bit adds the column number to the previous Even Number.

So now this part will be Odd when the column is Odd and Even when the column is Even.

MOD((ROW()-1)*8+COLUMN(),2)=0

The remainder of the formula is the same as the Zebra Stripes formula.

An Odd Number (Odd Columns) in the section above will return a 1 as the result of =Mod(Odd,2)

An Even Number (Even Columns) in the section above will return a 0 as the result of =Mod(Odd,2)

When evaluated against 0 will return True for Even Columns and False for Odd Columns.

Now the exact same happens in the False section of the If formula except that it is evaluated against 1.

I tackled this problem from a different direction to Chandoo.

Knowing that Even + Even = Even and Even + Odd = Odd and that the row and Column Numbers increase in each direction by 1 each Row/Column, it was simply a matter of adding the Row and Column numbers together and checking if it was Odd or Even

The Excel function IsOdd() and IsEven() both return a Boolean “True” if the contents are Odd or “Even” respectively. This negates an external truth check as described above.

This is easily shown by adding a formula to the Checker area

=Row()+Column()

Excel 2003: The above formula won’t work in Excel 2003.

Try this instead =Mod(Row()+Column(),2)=1

If the alternate shading is required a switch to

=ISEVEN(ROW()+COLUMN())

Does the trick.

Excel 2003: The above formula won’t work in Excel 2003.

Try this instead =Mod(Row()+Column(),2)=0

http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

and

http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

and

http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/

You can download a copy of the above file and follow along, Download Here.

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensics 001 – Tarun’s Problem

Formula Forensics 002 – Joyce’s Question

Formula Forensics 003 – Lukes Reward

Formula Forensics 004 – Freds Problem

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or this post.

If you have a formula that you don’t understand and would like explained but don’t want to write a post also send it in to Chandoo or Hui.

Spread some love,
It makes you awesome!

Posts & Navigation

Tags: Check-Board, column(), if(), iseven(), IsOdd(), Learn Excel, Microsoft Excel Conditional Formatting, Microsoft Excel Formulas, MOD(), row(), Zebra Stripes

RSS feed for comments on this post. TrackBack URI

View the original article here

Excel mashup tutorial

One of our Excel MVPs—Jan Karel Pieterse—emailed us earlier this week suggesting we take a look at the online tutorial he created about Excel Mashups. We did, and now we want to share it with you. 

In case you missed it, we recently blogged about ExcelMashup.com, a new site for developers which includes demo apps and code snippets for building Excel “mashups.”  If you don’t know exactly what a mashup is, you’re not alone—it’s just a web page that takes data from existing sources and combines it into something new. For example, a web guide to restaurants that consists of search results, Bing maps, and customer reviews.

You create an Excel mashup by uploading a workbook to SkyDrive and then embedding it on a web page. Then, you use JavaScript to programmatically interact with that workbook.

Jan Karel's tutorial:

Shows you how to embed a workbook on a web page.Provides JavaScript code snippets.Walks you through creating a web control.Provides a demo that pulls it all together.

And, you can read his instructions in Dutch or English! Have fun!

View the original article here

What is one area of Excel you want to learn more? [Survey]

AppId is over the quota
AppId is over the quota
Posted on December 2nd, 2011 in Learn Excel - 190 comments

It is almost weekend. Today we (Jo and I) are going to watch a cricket match being played in Vizag. We are pretty excited as this is the first time we are watching a match in stadium.

So, let keep this light and fun. I want to know What is one area of Excel you want to learn more?

I will go first. I want to learn more about Data Tables & Simulation.

What about you? Go ahead and tell us using comments.

Note: Here are a few choices if do not know what else is out there.

FormulasArray FormulasFormattingConditional FormattingChartingAdvanced ChartingPivot Tables & ChartsTablesData TablesValidationFilters & SortingVBA (Macros)Linking to Databases etc.SolverStatistical Analysis (regression, time series etc.)Scenarios, What if analysisDashboards

So go ahead and tell us.

RSS feed for comments on this post. TrackBack URI

View the original article here

Create smashing mashups with Excel

Today's post is brought to you by Larry Waldman, a program manager on the Excel team. You may remember Larry from his previous posts on embedding Excel files on web sites and designing Excel solutions for the Web.

Hello, web developers! Today, I want to point you to ExcelMashup.com, a new site from the Excel team that is designed to help you get started creating spreadsheet-based mashups. To create an Excel mashup, you use Excel Web App to create an embedded workbook that is stored on SkyDrive, and then displayed in a host web page. Once you've embedded the workbook, you use the JavaScript API to programmatically interact with it. 

To get started, do the following three things:

Sign up for a free SkyDrive account, if you don't already have one.Upload your Excel file to SkyDrive.Grab the JavaScript embed code snippet and start coding!

We've created some example mashups to help you see what's possible, including a Daily Calories Calculator and a Destination Explorer.

In this example, you search for a specific food, choose a serving size, and then see dietary information appear inside cells in an embedded workbook. This interactivity is accomplished by using the JavaScript API. This example also shows how to use the calculation and charting features of Excel Web App to render a chart that compares daily calorie intake to recommended dietary guidelines.

 

To learn how to recreate this example, click the text in the upper-right corner of the page. Then, to find sample code and instructions, click a specific area on the page, such as the search box or meal input worksheet. 

In this example, select a place to visit, and you'll see information about the area's average temperature and precipitation, along with monthly visitor patterns. (Fair warning: This information is intended only for illustrative purposes, so please don't rely on it for actual trip-planning activities!) You'll also see related information about your destination appear in Bing Maps.

 

To find out how to we created the Destination Explorer mashup, refer to this how-to guide.

We hope you're as excited as we are about interactive data mashups, and that you'll find ExcelMashup.com helpful as you create with your own. Feel free to ask questions in the Excel Web App forums or use the interactive code explorer, where you can experiment with our API right there on the site. Developers and JavaScript experts should feel right at home!

If you have comments, questions, or suggestions, please let us know by leaving a comment here or on ExcelMashup.com.

View the original article here

Now where on the network did I save my workbook?

I ask myself that question a lot, especially since I typically save my workbooks to SharePoint team sites on our corporate servers. If you, too, save and share files on your network, you can make your life easier by adding the Document Location box to the Quick Access Toolbar.  

The Document Location box shows you exactly where your file is saved. If you need your coworkers to review or edit your workbook, just paste the link from this box into an email message and send it to them. Trust me—sending a link is way better than sending a file attachment!

Here's how to add this box to the ribbon:

Click the arrow next to the Quick Access Toolbar, and then click More Commands. 

In the Excel Options dialog box, in the Choose commands from box, click Commands Not in the Ribbon.
Scroll down to the Document Location command and double-click it to add it to the Quick Access Toolbar.

Click OK. At this point, you should see the Document Location box on your Quick Access Toolbar. (If you ever want to get rid of it, right-click the border of the box, and then click Remove from Quick Access Toolbar.)

This procedure applies to Excel 2010, but it works pretty much the same way in Excel 2007. To quickly open the Excel Options dialog box in Excel 2010 or earlier, press ALT, and then press T, O.

-- Anneliese Wirth

View the original article here

When a macro won't cut it, try a VBA script

As an Excel MVP, I know that Excel is an extraordinarily powerful tool that can be used to simplify work in countless situations.  For example, fellow MVP Beth Melton recently designed a Gradebook template in Excel that makes teachers’ lives a whole lot easier. Based on Excel Tables technology which is built in to Excel 2010, the template includes grade calculation formulas and it even outputs a student progress report. All teachers need to do is select a student from the list, and the report is formatted and ready for printing.

Teachers who found this template on Office.com immediately liked what they saw and put it to use. Several teachers, however, wanted to make the template even more useful. They asked us whether the template could print all student progress reports at once.

To tackle this task, I knew recording a new macro wouldn’t cut it. It’s true that you could record a macro to print all of the class summaries in a batch. But this would not be a great solution because recorded macros are not smart. If the teacher were to add or delete students in the table, the macro would not function properly because the workbook would have been changed in a way that the recorded steps cannot fathom.

VBA is a fully fledged programming language built into Excel (and all other Microsoft Office applications, which supports variables (a place to store data) and programming logic.  This allows it to interact with virtually all of Excel’s built-in application objects, creating a much more powerful way than macros to automate user tasks. With it, I knew I could create a tiny program, integrated into the Gradebook template workbook, which would let teachers print summaries in a batch for all of the students no matter how many new students were added or deleted.

Logic and variables are what separate a program in VBA from a mere recorded macro. If you are familiar with the IF() worksheet function, then you know the difference. VBA has its own version of IF and several other constructs that allow for the execution of logic to branch, depending on circumstances. Add looping to the flow of execution and defined variables that can hold different values at different points in that flow, and a program can run circles around any recorded macro, even though both are “coded” in VBA.

Figure 1 clearly lays out the algorithm that is needed to implement printing the student summaries in a batch. All algorithms are directions (or a recipe) that guide your coding.  This one is simple:

Figure 1. Batch Print Algorithm.

All of the Office applications have a built-in editor for VBA. To display the editor simply press the ALT and F11 keys on the keyboard simultaneously.

 
Figure 2. Animation of how to insert a VBA code module.

On the left-hand side of the editor you will notice the Project Explorer, listing the worksheets in the Gradebook. While we could place VBA code in any of these worksheets, the best practice is to add a dedicated code module to the workbook. Just click on the Gradebook in the Project Explorer, and then select Module from the Insert menu at the top of the editor.

Notice that Module1 is added to the workbook. Now double-click on the module and you’ll be presented with a blank area on the right. This is where you enter the VBA code. The very first thing we should do at the very top of this code module is type the words: Option Explicit.

This is another best practice and should be at the top of all code modules. This forces the VBA interpreter to insist that all variables are defined before they are used. This may seem silly, but in larger programs this simple step can save untold grief when debugging your program.

To run our coded recipe, it needs to be contained in a defined subroutine. This is done by simply typing the keyword, Sub, followed immediately by the name we wish to assign to the routine. Let’s call ours PrintAllSummaries:

Sub PrintAllSummaries()

End Sub

Notice that as soon as we enter the line, the editor adds another line below it to terminate the subroutine container. All of the code for our little program will be entered between these two lines.

Next we need to define three variables that will be used while the program is executing:

        Dim i as Long
Dim sCount as String
Dim vStudents as Variant

Dim is the VBA keyword that defines a variable. It stands for dimension. So the first line here reads, dimension i as long. This creates a variable that we can refer to by the name of “i,” and it is dimensioned as a long integer. In VBA when you create variables, you specify what kind of data each variable is allowed to hold. A Long (integer) refers to numeric data with no decimal component. A String variable type is one that will hold text values. And a Variant is one that can hold any kind of data. So we know sCount will hold text and something interesting must be in store for vStudents.

Now that our variables are defined, the very next step is to specify what should happen if an error occurs during program execution:

    On Error Resume Next

Error handling is a large subject, but this little line of code is perhaps the easiest way to implement very basic error management. It simply tells the compiler to move on to the next line of code if any error is encountered. For many circumstances this would not be sufficient, but for our little program, this works well.

Now let’s get a list of all of the students in the class. It is possible to pluck them one at a time from the worksheet when needed, but another best practice is to retrieve all of the data at once. Fortunately this template comes with a defined name that references the column in the table that holds all of the student names: StudentLookup. It is currently being used to produce the data validation drop list in cell B8 of the Student Summary sheet.

So we can retrieve the entire list of students into our variant variable with the following line:

    vStudents = [StudentLookup]

What this line really does is place a two-dimensional array within vStudents. Arrays are variables that contain more than one value at the same time. A two-dimensional array has rows and columns, just like a worksheet. To get at the student names by referencing this array is hundreds of times faster than repeatedly referencing the worksheet cells to get the names. Not that it would make much difference on this tiny program, but again, best practices.

Now we are ready to code the algorithm. In actuality, the code to implement our algorithm is short, but we need to add some supporting code to verify that a teacher truly wishes the batch of reports to be sent to the printer. For example, a class of 35 students will result in 35 sheets of paper being printed. If the routine is run by accident, we had better give teachers a way to opt out.

To do that, we need to display a prompt that tells a teacher how many reports will be sent to the printer, and asks if they wish to continue. To do that properly, let’s temporarily set i equal to the number of elements in the array, which is exactly the number of students in the table:

    i = UBound(vStudents)

And now we can use our text variable to hold that number and the correct plurality of students:

         If i > 1 Then
sCount = i & " students "
Else
sCount = i & " student "
End If

The ampersand character joins strings of text together. Now we can use the text string stored in sCount as part of the prompt we display to the user. In VBA, the easiest way to prompt the user is to use the MsgBox command. MsgBox can be used like a function in this manner:

    If MsgBox("A progress report for " & sCount & "will be sent directly to the printer." _
& vbCr & "Do you want to continue?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"Student Gradebook") = vbYes Then

    End If

Notice the underscore characters at the ends of the lines. In VBA, these are line continuation characters. We use them so we do not need to scroll horizontally to see an entire line. So the first three lines are really one line of VBA code. vbYes is a built-in constant in VBA. If the user clicks the “Yes” button when the prompt is displayed, then MsgBox will return a value of vbYes.

Whatever we want to happen when the user clicks the “Yes” button should be placed between the IF and End If lines above. If the user does not click the “Yes” button on the prompt, then our little program will end because there will not be any more code below this block.

Now all that is left to do is place the coding of our recipe within the IF/End If block:

            For i = 1 To i
[StudentName] = vStudents(i, 1)
DoEvents
Sheet2.PrintOut IgnorePrintAreas:=False
Next

That’s the whole recipe, the algorithm from Figure 1, coded in VBA. Tiny isn’t it?

Notice the For/Next block? This is one of VBA’s many looping structures. It is very simple. It causes the flow of execution to loop a predefined number of times. If it read, For i = 1 to 10, then the flow would loop ten times and on each iteration, the value stored in the variable i would increase by one, starting with the value of one.

In our case, we are reusing the variable i. Since i already has a value before this loop is encountered, the loop will iterate whatever number of times is stored in i prior to the loop. Remember that number is equal to the number of students in the class.

So what happens in this loop? Cell B8 on the Student Summary worksheet was named, “StudentName” by the template builder. We can reference that cell by placing its name in square brackets. Consequently the following line will populate that cell with the current student in the list:

                [StudentName] = vStudents(i, 1)

Remember vStudents contains our two-dimensional array of student names, organized into rows and columns. Even though it has two dimensions, all of our students are in the first column, so we can hard code the value of 1 in the second index. Notice that the variable, i, is in the first index. This is where the rows are. So when i is one, the first student is retrieved from the array; when i is two, the second student is retrieved; etc. After we place the current student name in cell B8, we need to let the workbook recalculate and update the report to show the data for the current student. This actually happens automatically, but to be safe we can use the DoEvents command, which momentarily allows the operating system to process queued events.

Finally we print the report for the current student:

                Sheet2.PrintOut IgnorePrintAreas:=False

The code name for the Student Summary worksheet is Sheet2. This can be seen in the project explorer in the Visual Basic Editor. This allows us to refer to that sheet as simply Sheet2 from VBA. One of the many methods that worksheets expose to VBA is the PrintOut method, which prints the current sheet to the default printer. We call it via a dot after the sheet name. It has many optional parameters that can be tacked on at the end. We have chosen to honor the defined print areas, as defined on the Page Layout tab of the Ribbon. That’s it!

Remember that the For/Next loop ensures that a report is printed for each student.

Now save the workbook. You should select the *.xlsm file type when you do. This is for workbooks with macros, or VBA code in them.

Here is the entire subroutine:

Sub PrintAllSummaries()
Dim i As Long
Dim sCount As String
Dim vStudents As Variant

    On Error Resume Next

    vStudents = [StudentLookup]
i = UBound(vStudents)

    If i > 1 Then
sCount = i & " students "
Else
sCount = i & " student "
End If

    If MsgBox("A progress report for " & sCount & "will be sent directly to the printer." _
& vbCr & "Do you want to continue?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"Student Gradebook") = vbYes Then

            For i = 1 To i
[StudentName] = vStudents(i, 1)
DoEvents
Sheet2.PrintOut IgnorePrintAreas:=False
Next

    End If
End Sub

As you can see, this routine is custom coded to enable batch printing of the reports produced by this particular workbook. Similar techniques can be employed to create a custom batch printing solution for just about any workbook. The key is updating the correct cell or cells with the appropriate data and iterating the proper number of times. These details will be different in every situation, but the technique is the same. To run the routine, you can select it from the Macro dialog in the normal Excel window. Press ALT-F8 on the keyboard to bring it up. Alternatively, you can insert a text box on the worksheet and assign the macro to run when the text box is clicked. To do so, right-click the text box and select Assign Macro, and then select PrintAllSummaries from the list.

With Visual Basic for Applications you can automate virtually every part of Excel, and all of the other Microsoft Office applications as well. While this article has demonstrated an almost trivial (yet very useful) solution using VBA, truly impressive custom solutions can be developed this way. This author has made a living doing just that for a very long time. VBA is a subset of the venerable Visual Basic 6.0 general purpose programming language. Microsoft very cleverly incorporated this subset into the Office Suite, while at the same time exposing hundreds of native Office objects to the programming language. VBA’s heritage is much older than Excel, but the marriage of the technologies has produced an incredibly potent tool to develop custom business solutions.

To take a peek at the working script, download the Excel 2010 gradebook template.

--Daniel Ferry

Daniel Ferry is a Microsoft Excel MVP and solution developer. He operates an Excel enthusiast website and blog called Excel Hero. He also operates the Excel Hero Academy, where he teaches advanced Excel, online, to students from all over the world.

View the original article here

Poll your friends with Photo Vote

Having trouble deciding which movie to see, which outfit to buy, or which celebrity hairstyle to copy? You're in luck, because our very own Office Labs team just released Photo Vote, a free Facebook app that lets you poll your friends on, well, anything you want! (Fair warning: people may decide you need a Bieber.)

In Facebook, create a poll with up to three photos and captions, and then post it on your wall. As your friends vote on the options, Photo Vote collects their votes, so you can see which choice is most popular. When the votes are in, you can interact with the results in an embedded spreadsheet, or take it a step further and geek out on the data in Excel. 

From start to finish, creating a poll should take about 5 minutes, tops. You can create polls in Facebook or on the go, from your Windows Phone.

First things first, you need the Photo Vote app.

 For this post, I'll walk you through the process of creating a poll in Facebook.

In Facebook, click Create a Poll, and then add a title, along with photos and captions. 

To post your poll on your Facebook wall, click Publish.

Photo Vote collects the votes for you and voila! The people have spoken:

Now, this is the Excel blog, and we're all data-heads here, so you know what's coming next...

To slice-and-dice poll results, click the Experience the Data link underneath your photos:

The results of your poll now appear in an embedded spreadsheet:

You can play around with the data by clicking the gender, relationship status, and age range filters (slicers) that appear to the left of the poll results.

Here are a couple of other things you might try:

To close the embedded spreadsheet and return to the poll view, click the Return to Poll View link underneath the black bar.

Photo Vote is brought to you by two teams here at Microsoft—our Office Business Intelligence team, and our Office Labs team, which is a group that test drives new concepts and ideas. Photo Vote is available through the end of the year, and we want to know what you think about it. If you're so inclined, please leave a comment on this post or in the Photo Vote forum.

Have fun!

-- Anneliese Wirth

View the original article here

Formula Forensics. No 007 – Sumproduct

One of the most asked questions within the posts and Forums at Chandoo.org is “How Does Sumproduct work ?”.

Rahul recently asked for an example in Excels Sumproduct Formula post;  Comment No. 55.

So today in Formula Forensics we will take a look at just that with a few worked examples.

Excels help defines Sumproduct as:



So what are these arrays referring to:

An array in Excel can be :

A manual Array:     {10;20;30}

A Range:              A1:A3

A Named Range: MyRange1

Where MyRange1 is defined as a defined range in the Name Manager.

A Named Formula: MyRange2

Where MyRange2 is defined as a Formula returning a range in the Name Manager.

Lets look at each

You can follow along in the Example file on Sheet1

An Array

In C2 type: =SUMPRODUCT({10;20;30})

Excel will display 60, which is the Sum of the array elements =10+20+30

A Range

C7:          =Sumproduct(C4:C6)

Excel displays 60, which is the Sum of the cells from the range C4:C6 =10+20+30

A Named Range

In the Name Manager or Name Box define a Named Range

MyRange1:         =Sheet1!$C$4:$C$6

Then in C10 type:

C10: =Sumproduct(MyRange1)

Excel displays 60, which is the Sum of the range elements =10+20+30

A Named Formula

In the Name Manager define a Named Formula

MyRange2          =OFFSET(Sheet1!$C$3,1,0,3,1)

Then in C12 type:

C12:       =Sumproduct(MyRange2)

Excel displays 60, which is the Sum of the range elements from cells C4:C6 =10+20+30

You may be asking why use Sumproduct when we can use a simple Sum to add up 3 numbers?

The answer is to show you what Sumproduct is doing, it is Adding up each Array element.

Remember back at the start where we saw the Definition of Sumproduct,

SUMPRODUCT(array1, [array2], [array3], …)

Only Array 1 is required, Array 2, Array 3 etc are optional, that’s what the square brackets [ ] mean.

Goto Sheet 2 in the Example file:

We will look at a simple example using two arrays



The data consists of Sales data.

Often we want to know what the total sales are

We do this by  adding a Sales column



Which multiplies the Qty and Price columns

And then Sum (Add) up this new column



Returning our Total Sales of 15,000

Now we can manually check the above as the numbers are simple eg: 100*20 = 2,000 etc

And we can sum up the Sales and see that we in fact had total sales of 15,000

Well this is exactly what Sumproduct is made to do:

In a Blank cell enter: =SUMPRODUCT(D4:D8,E4:E8)



Excel will return 15,000.

So what is Sumproduct doing?

Lets look inside and see what’s going on

In the Example File, Sheet2, H1 there is a copy of the data laid out as below



Note that our formula =SUMPRODUCT(D4:D8,E4:E8)

Has two Arrays

Array 1: D4:D8

Array 2: E4:E8

Note that each corresponding Array Element is multiplied together

100 x 20

20 x 200 etc

These are the products of the two Arrays

Finally the Products are Added together and the correct answer 15,000 is returned.

So Sumproduct is the Sum of the Products of the Arrays

Of course we can extend that to a large number of Arrays, columns in this case, if we wish.

In the above two examples we saw that Sumproduct can Sum a single Array and can Sum the Product of two or more Arrays.

We can use that to our advantage and build logic into the arrays, allowing us to optionally include some array elements and leave out others.

Sumproduct will always add up the product of all Arrays.

So by including an Array where the elements within the Array that we don’t want to Sum are Zero and the Elements within the array that we do want to Sum are 1 we can control what is included in the final Summation.

Goto our Example File on Sheet3

Lets say we only want to include the Sales from our Northern Region

One way to do this is to purely delete the other entries



But what if we could do that without altering our worksheet or there are thousands of rows of data?

This is where Sumproduct comes into its own.

What we need to do is add some logic to our equation, effectively doing:



Lets try it with Sumproduct

In Cell F12: type =SUMPRODUCT(D4:D8,E4:E8,{FALSE;TRUE;FALSE;FALSE;TRUE})

Excel displays a -

Excel doesn’t know what to do with the True/False and so converts them to 0

We can force excel to evaluate these as numbers by adding a simple “1*”

In F14: Type =SUMPRODUCT(D4:D8,E4:E8,1*{FALSE;TRUE;FALSE;FALSE;TRUE})

Excel now displays 5,000 the total sales from the North

To see what has happened in F16 type: 1*{FALSE;TRUE;FALSE;FALSE;TRUE}, but don’t press Enter press F9 instead.

Excel displays ={0;1;0;0;1}

The use of the 1* has converted each of the Array elements from a True/False to a 1,0 respectively.

So our 3 arrays are now:



Now adding an Array of 1*{FALSE;TRUE;FALSE;FALSE;TRUE} every time we wanted to add some numbers isn’t a practical solution.

Excel has the ability to work construct an Array on our behalf!

In E18: enter  =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=”North”))

Excel will display 5,000

So 1*(C4:C8=”North”) is exactly equal to our previous array 1*{FALSE;TRUE;FALSE;FALSE;TRUE}

1*(C4:C8=”North”) = 1*{FALSE;TRUE;FALSE;FALSE;TRUE}

At the heart of this is that Excel is evaluating each cell in the Range: C4:C8 against our required logic =”North” and setting up an Array for us internally.

The power of Sumproduct is therefore in that we can now simplify and extend

In cell E20 type: North

In cell F20 type: =SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=E20))

Excel will display 5,000

This simple addition allows us to vary the Summation based on the value in E20

We don’t need to multiply our logic array by 1, we can actually use any number or another Array.

In cell F22 type: =SUMPRODUCT(D4:D8,(E4:E8)*(C4:C8=E20))

This works as (C4:C8=E20) is returning an Array of True/False which get converted to an array of 1/0’s when subject to any maths.

The Math in this case is the multiplication by the 2nd Array (E4:E8)*(C4:C8=E20)

In Cell F24 type: =SUMPRODUCT(Qty, Price *(Region=SalesRegion))

Excel will display 5,000

But notice that by using Named Ranges/Formula how simple the logic of the equation has now become.

In Comment No. 55: Rahul asked, “Can you give an example work sheet of above example”

Sheet 4 in the Example File is the answer.



In Cell C23: type: =SUMPRODUCT(- -(A2:A21=”Luke Skywalker”),- -(B2:B21=”West”),C2:C21)

Excel will display 141, which is the sum of the Sales made by Luke Skywalker in the West Region.

However using what was learned above, this is better simplified to:

C26: =SUMPRODUCT((Name=SalesMan)*(Region=SalesRegion)*Sales)

In the formula above Chandoo has used what is known as a Double Unary, which is 2 – signs next to each other (I have inserted a space above to make it more legible).

Two – signs are the same as saying

- -(A2:A21=”Luke Skywalker”) = -1 x -1 x (A2:A21=”Luke Skywalker”)

-1 x -1 is 1

Technically this is the most efficient way for Excel to perform any maths on the Array

- -(A2:A21=”Luke Skywalker”)

So that the Array of true/Falses made by (A2:A21=”Luke Skywalker”) is converted to an Array of 1/0’s for use in Sumproduct.

At the slight expense of speed but for improved readability and understandability by others I prefer the use of 1* instead of - – and you will mostly see that convention in my posts.

Chandoo:            - -(A2:A21=”Luke Skywalker”)

Hui:                       1*(A2:A21=”Luke Skywalker”)

In fact any maths performed on the array will convert its contents to an array of 1/0’s, so long as the maths doesn’t change the Arrays values

For a real good discussion on this topic have a look at the post The Venerable SUMPRODUCT at ExcelHero.com

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

http://chandoo.org/wp/tag/sumproduct/

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

You can download a copy of the above file and follow along, Download Here.

You can learn more about how to pull Excel Formulas apart and what makes them tick in the following post:

Formula Forensic Series:

I am running out of ideas for Formula Forensics and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or like above.

If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui.

This will be the last Formula Forensics Post for 2011, but rest assured that we will be returning in early 2012.

I’d like to take the opportunity to thank Chandoo for allowing me the space and freedom to post pretty much what ever I’ve wanted at Chandoo.org. I hope you have enjoyed my contributions to the Chandoo.org community over the past year.

On behalf of Eva and myself I’d like to wish you all a very Merry Xmas and a Happy and Safe New Year ahead


RSS feed for comments on this post. TrackBack URI


View the original article here

Excel 2010 Bible

A comprehensive reference to the newest version of the worldâ??s most popular spreadsheet application: Excel 2010

John Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. Known as "Mr. Spreadsheet," Walkenbach shows you how to maximize the power of all the new features of Excel 2010.

An authoritative reference, this perennial bestseller proves itself indispensable no matter your level of skill, from Excel beginners and intermediate users to power users and potential power users everywhere. Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques you wonâ??t find anywhere else.

• Excel guru and bestselling author John Walkenbach ("Mr. Spreadsheet") guides you through every aspect of Excel
• Delivers essential coverage of all the newest features of Excel 2010
• Presents material in a clear, concise, logical format that is ideal for all levels of Excel experience
• Includes a CD that contains all the templates and worksheets used in the book plus John Walkenbach's award-winning Power Utility Pak

Excel 2010 Bible serves as an excellent resource on all things Excel!

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Price: $44.99

Click here to buy from Amazon

Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf)

• This book is a single reference thatâ??s indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere
• Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers wonâ??t find anywhere else
• John Walkenbach, aka "Mr. Spreadsheet," is one of the worldâ??s leading authorities on Excel
• Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features
• Includes a valuable CD-ROM with templates and worksheets from the book

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Price: $49.99

Click here to buy from Amazon

New Selectsoft Publishing 101 Greatest XP Apps & Utilities For Powerful Digital Photography

Now you can have the greatest Windows XP applications and utilities without spending hundreds of hours searching and downloading them. The Windows Users Group Network (WUGNET) has compiled these amazing shareware applications and utilities for you on one CD-ROM with an easy-to-browse launcher.

Price: $13.41

Click here to buy from Amazon

NeatReceipts Scanalizer Professional 2.5 Mobile Scanner and Software

Amazon.com Product Description Please note: A 3.0 software upgrade for the NeatReceipts Scanalizer will be released on October 15, 2007. You may purchase the software upgrade at the NeatReceipts website: Neatreceipts.com/Upgrade

NeatReceipts Version 2.5 Professional is the easiest way for individuals and small businesses to manage expenses, prepare for taxes, create expense reports, and scan business cards. But it not all just for business--you can also use Neat Receipts for scrapbooking, archiving family recipes, and other family activities. NeatReceipts Version 2.5 Professional edition also includes a new Document Organizer feature in addition to Receipts, Business Card, and Tax Organizers features.

Use NeatReceipts 2.5 to manage expenses, prepare for taxes, create expense reports, and scan business cards. View example.

Organize expenses by IRS form and category. View example.

Sync contact information into Outlook, Plaxo or vCard. View example.

Scan receipts and save in digital spreadsheet format. View example.

Scan documents, recipes, and more and then convert them to PDF. View example.

How NeatRecipts can Make Your Life Easier

Make permanent digital copies of all your financial papers
NeatReceipts 2.5 comes with a high resolution portable scanner. All you have to do is scan your receipts, invoices, and other financial documents through this scanner, and our software will save permanent copies that you can depend on for years to come.

Eliminate financial data entry, simplifies expense tracking
Using patent-pending NeatScan technology, NeatReceipts 2.5 extracts all important information from each receipt including date, amount, vendor and payment type. It then organizes all this expense information into a searchable expense database.

Speed up tax preparation
NeatReceipts allows you to leave digital comments on receipts and mark them for tax purposes. During tax season, retrieve all the marked receipts with ease and review those comments for easy preparation. NeatReceipts stores each receipt in a secure digital format that is accepted by the IRS.

Integrate with QuickBooks, Quicken, Money, Act!, Peachtree
Export to QuickBooks, MS Money, Quicken and most other financial software. By automating data entry and making it easy to export to other software, NeatReceipts makes sure you never have to type information into your financial software again!

Scan business cards
NeatReceipts captures key contact information from business cards and syncs with your Outlook address book. You can also create V-cards to upload to your PDA.

Automatically create expense reports
Automatically creates expense reports from scanned receipts and attaches images of the original receipts for simple verification. Users can email these reports from within the software or export them into PDF, DOC, XLS or HTML formats. If you are looking for expense reporting software with in-built approval, analysis and administration capabilities, please consider NeatReceipts Enterprise.

How NeatReciepts Works

NeatScan Technology Using advanced Optical Character Recognition, NeatScan technology (patent pending) extracts key information from your receipts, such as date, amount, payment method, expense category and vendor. If you scan in business cards, NeatScan automatically pulls out information like name, company, address, phone numbers, email address etc.

NeatScan actually learns to associate an expense category (such as meals or lodging) to a particular vendor (brands such as Starbucks or Hilton) so you don't have to type in the information each time. This ensures that your financial records are filed consistently and with minimal effort.

Each user of NeatReceipts Professional 2.5 receives the NeatReceipts scanner--a lightweight, portable, durable color scanner. Our software was designed with this scanner in mind and offers the following unmatched functionality:

• Auto-sizes any scanned receipt or document.
• Auto-crops and auto-rotates any scanned receipt or document.
• Scan widthwise or lengthwise -- the image will appear upright in image viewer.
• Powered by USB port, so no plug needed (it's truly portable!).
• Scans quickly and easily without opening and closing scanner lid (flatbed).
• Small, portable and lightweight. Perfect for the office, the home and the road!

What New with NeatRecipts 2.5

NeatReceipts Professional 2.5 offer many new features like recognition of credit cards by last 4 digits, simplified and enhanced Advanced Fast Find for your folder and receipts, and added printing options that let you print one to four images per page and choice of paper size.

Just like its previous version, NeatReceipts Professional 2.5 includes a business card reader at no additional charge. Simply scan your business cards and sync your contact information automatically with your Outlook address book, or create a V-Card for your palm pilot. Version 2.5 has the added convenience of Plaxo integration, so you can just go online, click "sync with Plaxo" and have your contact information updated.

New in business card features in version 2.5 include an improved interface and parsing, both black & white and color scanning, one-click e-mail to and browse-to options, and completed action item check box column.

Also, for the first time, NeatReceipts Scanalizer Professional Version 2.5 also includes a document organizer so you can keep track of all types of documents and access them up as needed. Document Organizer features include:

• Black & White and Color Scanning
• Document copier
• Copy Document command
• Ability to combine documents together
• Ability to separate multi-page documents

What's in the Box
Scanner, carrying case, stand

Price: $209.95

Click here to buy from Amazon

Learn Excel 2007 Essential Skills with the Smart Method: Courseware Tutorial for Self-Instruction to Beginner and Intermediate Level

This book is very, very different to any other computer book you have ever read. The Smart Method provides an entirely new and better way to learn Excel. Here are just five of the many ways in which this book is unique:

• The book will equip you with excellent Excel skills, good enough to impress any employer, but it doesn’t confuse by attempting to teach skills that are not common in the workplace. Only users who have advanced requirements need progress to the Expert Skills book.
• This is the only book that is also available as a low-cost video (DVD). Each can be used as a stand-alone product, but together they provide an incredibly powerful learning experience.
• Instruction is logically structured into sessions and lessons. While this makes the book ideal for self-instruction, you can also use it to run your own, highly effective, adult training courses.
• Every lesson is presented on two facing sheets of A4 paper (the pages are much bigger than in any other Excel book).
• Each lesson has a sample file that models a real-world business problem. You’ll immediately appreciate the value and relevance of each skill as it is taught.

Both IT professionals and absolute beginners will love this book because it avoids needless technical jargon and concisely explains everything you need in a simple and no-nonsense way.

Price: $21.99

Click here to buy from Amazon

BRAND NEW Microsoft Excel 2010 1 PC Spreadsheet Complete Product DVD-ROM PC English

Manufactured to the Highest Quality Available. Marketing Information:Microsoft Excel 2010 makes it possible to analyze, manage, and share information in more ways than ever before, helping you make better, smarter decision. Whether you're producing financial reports or managing personal expenses, Excel 2010 gives you more efficiency and flexibility to accomplish your goal.Distribution Media/Method:DVD-ROM. Product Type:Software. Product Information. Software Main Type:Productivity Application. Software Sub Type:Spreadsheet. Software Name:Excel 2010. Features:Create data charts in a single cell. Zero in on the right data points quickly. Efficiently model and analyze almost any data. Access your spreadsheets from virtually anywhere. Connect, share, and accomplish more when working together. Add more sophistication to your data presentations. Take advantage of more interactive and dynamic PivotCharts. Do things easier and faster. Harness more power for building bigger, more complex spreadsheets. Publish and share through Excel Services. Language Support:English. Platform Support:PC. Operating System Support:Windows. License Information. License Type:Complete Product. License Quantity:1 PC.

Price: $251.30

Click here to buy from Amazon

Excel 2007 For Dummies

One look at Excel 2007, with its new Office Button, Quick Access toolbar, and Ribbon, and you realize youâ??re not in Kansas anymore. Well, have no fearâ?? Excel 2007 for Dummies is here!

If youâ??ve never worked with a computer spreadsheet, or if youâ??ve had some experience with earlier versions of Excel but need help transitioning, here youâ??ll find everything you need to create, edit, format, and print your own worksheets (without sacrificing your sanity!). Excel 2007 for Dummies covers all the fundamental techniques, concentrating on only the easiest, most user-friendly ways to get things done.

Youâ??ll discover how to:

• Rearrange, delete and insert new information
• Keep track of and organize data in a single worksheet
• Transfer data between the sheets of different workbooks
• Create a chart using the data in a worksheet
• Add hyperlinks and graphics to worksheets
• And more!

Plus, in keeping with Excel 2007â??s more graphical and colorful look, Excel 2007 for Dummies has taken on some color of its own, with full-color plates in the mid-section of the book illustrating exactly what youâ??ll see on your screen. Whether you read it from cover to cover or skip to the sections that answer your specific questions, the simple guidance in this book will have you excelling at home or in the office no time.

Price: $21.99

Click here to buy from Amazon

Office 2010 All-in-One For Dummies

The leading book on Microsoft Office, now fully updated for Office 2010

Microsoft Office, the world's leading productivity suite, has been updated with new tools. Veteran Office users as well as newcomers will need the comprehensive information in this bestselling All-in-One guide.

With a self-contained minibook devoted to each Office application plus minibooks on how Office works together and how you can expand its usefulness, Office 2010 All-in-One For Dummies gets you up to speed and answers the questions you'll have down the road.

• Microsoft Office is the office productivity suite used around the globe; nearly every business worker encounters it daily
• The 2010 revision will affect all applications in the suite
• Eight minibooks cover Word, Excel, PowerPoint, Outlook, Access, Publisher, common Office tools, and ways to expand Office productivity
• Also covers the new online versions of Word, Excel, and PowerPoint as well as changes to the interface and new tools and techniques

Office 2010 All-in-One For Dummies makes it easy to learn to use Office and gets you up and running on all the changes and enhancements in Office 2010.

Price: $34.99

Click here to buy from Amazon

The Neat Company NeatScan To Office Scans to Excel Outlook Word and PowerPoint (White)

SCANNER, NEAT SCAN TO OFFICE, USB 2

Price: $279.95

Click here to buy from Amazon

Vending Machine Company Business Plan - MS Word/Excel

The Vending Machine Company Business Plan is a comprehensive document that you can use for raising capital from a bank or an investor. This document has fully automated 3 year financials, complete industry research, and a fully automated table of contents. The template also features full documentation that will help you through the business planning process. This is a full and complete business plan with original research, financial models, and marketing/advertising plans that are specific for a Vending Machine Company. Since 2005, BizPlanDB and its parent company have helped raise more than $100,000,000 through its developed plans.

Price:

Click here to buy from Amazon

Wine Cellar 2.1 Template for Microsoft® Excel Lets You Easily Track Your Personal Wine Collection

Wine Cellar 2.1 Template for Microsoft® Excel lets wine enthusiasts easily track and manage personal wine collections. The template saves people the time of setting up their own system. Data entry is easy and wines can be sorted by varietal, pairing, when to drink, quantity, price and many other categories. The spreadsheet can be customized to match different collections. Please note that the template is delivered via email. Added information: Very important; the template is designed for personal use where someone doesn't want to have to stand in their cellar or thumb through a notebook trying to decide what to drink. If you are in a business where you have to manage wine inventory, this is not the right solution. You need inventory management software.

Price:

Click here to buy from Amazon

Excel 2007 Bible

• This book is a single reference thatâ??s indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere
• Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers wonâ??t find anywhere else
• John Walkenbach, aka "Mr. Spreadsheet," is one of the liworldâ??s leading authorities on Excel
• Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features
• Includes a valuable CD-ROM with templates and worksheets from the book

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Price: $39.99

Click here to buy from Amazon

Apple iWork '08 [OLD VERSION]

Item #: 35072F. Apple's amazing productivity suite for the Mac, iWork '08 includes three applications: Pages '08 for word processing with an incredible sense of style; Numbers '08 for powerful, compelling spreadsheets made easy; and Keynote '08 for cinema-quality presentations for everyone.

Product Description
IWork '08 - complete package
Category: Office applications
Subcategory: Office applications - presentation, office applications - word processor
License Type: Complete package
License Qty: 1 user
License Pricing: Standard
Platform: MacOS
Distribution Media: CD-ROM
Package Type: Retail
OS Required: Apple MacOS X 10.4.10
Customers also search for: Discount IWork '08 - Complete Package - 1 User - CD - Mac, Buy IWork '08 - Complete Package - 1 User - CD - Mac Wholesale IWork '08 - Complete Package - 1 User - CD - Mac, 0885909140244, MA790Z/A, Photo Editing Software

Price: $74.00

Click here to buy from Amazon

Use copy paste & to maintain references to tables [Quick Tip]

Posted on 23 May 2011 to learn Excel-9 comments

Using Excel 2007, Microsoft introduced powerful and useful feature called as tables. One advantage of tables that you can write formulas readable using structural references. This means, you can write easy to understand such formulas,

[Help SUMIF formula]

But there is a problem. When you type this formula and dragging formula cell remaining side fill cells, Excel changes the reference columns of the table and thus makes your formulas almost useless.

In addition, there is a simple solution for this problem.

Instead of dragging the cell to fill the formulas, you can use the copy paste to fill formulas &. In this case, Excel preserves all references table while changing cell references and accordingly. Watch this demo to understand:

Since the found feature tables in Excel 2007, I have been using them to save time and simplify working with data. Schedules of many useful features that make life simple for analysts and data junkies everywhere.

What about you? Are you using Excel tables? What are the tricks above your table? Participation please use the comments.

If you use Excel 2007 or above, I encourage you to learn Excel tables. They make your life simpler. Go to the bottom of articles for more information,

Spread some love
It makes you fabulous!

& Navigation functions

Tags: data Excel Excel Excel tables, see "Paste" and "quick tip", "References" and "screencasts" and "spreadsheets" and "structured references", "sumif)

RSS feed for comments on this post. TrackBack URI

View the original article here

Lost Excel functions

Error in deserializing body of reply message for operation 'Translate'. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 2, position 9250.
Error in deserializing body of reply message for operation 'Translate'. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 1, position 13902.
Posted on May 16th, 2011 in Excel Howtos , Huis , Posts by Hui - 26 comments

Following on from Chandoo’s MLookup function published on 1st April 2011, I thought it might be worth documenting a few undocumented, no-longer documented and rarely used Excel functions.

Although some of the functions below aren’t documented they still work as of Excel 2010.

Users should be cautious with their use going forward as Microsoft may withdraw them from future versions of Excel.

But if you see them appear in older Excel models at least you’ll be the full bottle.

This post will look at the following functions:

DatedifRoundupRoundDownEvaluateConvertRomanFactDoubleBahttext

Worked examples of all these functions are presented in the Example File which is compatible with all versions of Excel.

The DATEDIF function computes the difference between two dates in a variety of different intervals, such number of years, months, or days.

This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000.

By the way, do not confuse the DATEDIF worksheet function with the VBA DateDiff function.

=DATEDIF(Start Date, End Date, Interval)

Where:
Start Date must be less than the End Date.

Interval is the interval type to return.

Interval value must be one of the following:

Complete calendar months between the dates.Number of days between the dates.Complete calendar years between the dates.Complete calendar months between the dates as if they were of the same year.Complete calendar days between the dates as if they were of the same year.Days Excluding Years And MonthsComplete calendar days between the dates as if they were of the same month and same year.

If you are including the Interval string directly within the formula, you must enclose it in double quotes:

=DATEDIF(Date1,Date2,”m”)

Datedif has been used a number of times at Chandoo.org

http://chandoo.org/forums/topic/how-to-calculate-age-from-their-dob

http://chandoo.org/wp/2009/09/22/elapsed-time-excel/

http://chandoo.org/wp/2008/08/26/date-time-tips-ms-excel/

Disclaimer:

Although the Datedif function above isn’t documented it still works as of Excel 2010. Users should be cautious with their use going forward as Microsoft may withdraw support for them in future Excel versions.

The Roundup and Rounddown functions rounds a number up or down, away from zero and have pretty much been replaced by the Round function.

The Roundup function rounds a number up, away from zero.

=ROUNDUP(number, num_digits)

The Rounddown function rounds a number down, towards zero.

=ROUNDDOWN(number, num_digits)

Roundup() behaves similarly to the Round() function, except that it always rounds a number up based on the following rules:

If num_digits is greater than 0, then number is rounded up to the specified number of decimal places.If num_digits is 0 or omitted, then number is rounded up to the nearest integer.If num_digits is less than 0, then number is rounded up to the left of the decimal point.

ROUNDUP(4.1,0) equals 5

ROUNDUP(106.9,0) equals 107

ROUNDUP(3.14159, 3) equals 3.142

ROUNDUP(-3.14159, 1) equals -3.2

ROUNDUP(31415.926, -2) equals 31500
Rounddown() behaves similarly to the Round() function, except that it always rounds a number down based on the following rules:

If num_digits is greater than 0, then number is rounded down to the specified number of decimal places.If num_digits is 0 or omitted, then number is rounded down to the nearest integer.If num_digits is less than 0, then number is rounded down to the left of the decimal point.

ROUNDDOWN(4.1, 0) equals 4

ROUNDDOWN(106.9,0) equals 106

ROUNDDOWN(3.14159, 3) equals 3.141

ROUNDDOWN(-3.14159, 1) equals -3.1

ROUNDDOWN(31415.92654, -2) equals 31400

The Roundup and Rounddown functions have been used several times at Chandoo.org

Roundup

http://chandoo.org/wp/2010/04/29/quarterly-totals-from-monthly-data/

http://chandoo.org/wp/2010/04/30/quarterly-totals-multi-year-data/

Rounddown

http://chandoo.org/wp/2010/04/30/quarterly-totals-multi-year-data/

http://chandoo.org/wp/2009/07/06/excel-formulas-round-sort/

Evaluate is an Excel ver 4.0 macro function which is still supported and functional in Excel 2010.

The Evaluate function allows for the evaluation of a text equation as an algebraic equation.

The evaluate function cannot be used as a spreadsheet function but can be used in Named Ranges.

It is probably best described by example; Evaluate 1, from the Example File.

Say you have a polynomial equation in a cell as Text A1: ‘=X2 + 5*Y – Z

Setup 3 named ranges, X, Y , Z with values X=10, Y=5 and Z=3

You can use Evaluate in a a Named Range eg: Result =Evaluate(SheetName!$A$1)

And then on a worksheet =Result, which will return the answer 122 = 102 + 5*5 – 3

Evaluate can be used to allow graphing of equations without use of worksheet functions or even worksheet ranges, an example of each is shown in the examples file as Evaluate 2 and Evaluate 3 .

Evaluate 2: Uses a Range as the X Values and a Named Range using the Evaluate function as the calculated Y Values

Evaluate 3: Uses Named Ranges as the X Values and as the calculated Y Values based on an Evaluate function

Use of the Evaluate function on Chandoo.org:

Not Used

Converts a number from one measurement system to another.

For example, CONVERT can translate a table of distances in Kilometres to a table of distances in Miles.

Convert includes 49 units spread amongst the following 10 categories

=Convert(number, From Unit, To Unit)

A list of all the Conversion Units and Conversion Prefixes is included on the Conversion Factors tab of the Examples File.

Converts a 5 pound mass to kilograms (2.267)Converts 80 degrees Fahrenheit to Celsius (26.6)Data types are not the same so an error is returned (#N/A)‘=CONVERT(CONVERT(100,”ft”,”m”),”ft”,”m”)Converts 100 square feet into square meters (9.290304).

A list of all the Conversion Units and Conversion Prefixes is included on the Conversion Factors tab of the Examples File.

http://chandoo.org/forums/topic/convert-function

The Roman function converts a number to Roman format.

=ROMAN(number, form)

=ROMAN(45 ) = XLV

Form is a number specifying the type of roman numeral you want. The roman numeral style ranges from Classic to Simplified, becoming more concise as the value of form increases.

More concise. See example below.More concise. See example below.More concise. See example below.Converts 499 to Roman (CDXCIX)Converts 499 to Roman (CDXCIX)Converts 499 to Roman (LDVLIV)

Nil

Factdouble returns the double factorial of a number and is expressed in mathematics as n!!

Double factorials are used in probability theory and other higher levels of mathematics and is really just a way to simplify an otherwise complex expression

If the number is Even Factdouble = n(n-2)(n-4)…(4)(2)

If the number is Odd Factdouble = n(n-2)(n-4)…(3)(1)

So it is simpler to write 10!! than 10x8x6x4x2

=Factdouble( number )

Factdouble of 8 = 8x6x4x2 = 384Factdouble of 9 = 9x7x5x3x1 = 945

Not used

Converts a number to Thai Text represention of the number

Use:

=Battext( Number)

=Bahttext(250) , Returns

Not used

An example file with worked examples from all the above functions is available from the following link; Example File

The file is compatible with all Excel versions.

What Functions Have You Stumbled Onto?

Let us know in the comments below:

Spread some love,
It makes you awesome!

Posts & Navigation

Tags: Convert, Datedif, Evaluate, FactDouble, Learn Excel, Microsoft Excel Formulas, Roman, rounddown(), roundup, spreadsheets

RSS feed for comments on this post. TrackBack URI

View the original article here

Sumproduct advanced queries

Posted on 26 May 2011 at Excel Howtos, learn Excel functions of observable by Hui-34 comments

Use the function Sumproduct for multiple criteria in one situation may amount attachments larger than Excel function beyond what it was designed primarily for already designed may with that in mind?

However, Sumproduct also can extend even through the use of 2D ranges along with carefully constructed queries.

Examples are provided below in "example" Excel 2003 is an example of a file.

Your schedule and sold fruit and sold every day

How many bananas sold on 4thMay?

The previously named ranges Setup 3

Use named ranges as it facilitates reading the future versions.

Fruit: C2: H2

Dates: B3: B12

Froitdata: C3: H12

So, how many bananas do not sell to 4thMay?

Use = SUMPRODUCT ((Fruit = D16) * (Date = D15) * FruitData) equation

Returns the correct 31answer

Relevant: searching 2way in Excel

Your schedule and "car" and sold every day. There are several entries on different days, perhaps from different vendors.

How many cars Holden sells on May 3rd?

So, how many cars did not sell in Holden may 3rd?

Use = SUMPRODUCT ((Dates = D17) * (Cars = D18) * CarData) equation

Returns the correct answer 9 = (1 + 5 + 3)

Your schedule and "car" and sold every day, and there are multiple entries for different days.

How many vehicles Ford and Suzuki sells in May 10th?

So, how many vehicles Ford and Suzuki does not sell in May 10th?

Equation = SUMPRODUCT ((Dates = D24) * ((Cars = D25) + (auto = E25)) * as ardata)

Returns the correct answer 13 = (4 + 5 + 3 + 1)

Note that this can extend to add additional queries where you can enter "vehicle type" in any cell in the range D25: H25

= SUMPRODUCT ((Dates = D24) * ((Cars = D25) + (auto = E25) + (auto = F25) + (auto = G25) + (auto = H25)) * as ardata)

Your schedule and "car" and sold every day, and there are multiple entries for different days.

How many cars Toyota and Holden sells in May 10th?

How many cars Toyota and Holden sells in May 10th?

Use = SUMPRODUCT ((Dates = D30) * (Cars = D31: H31) * CarData) equation

Returns the correct answer 21 = (3 + 6 + 6 + 6)

Note that this can be extended to allow additional queries, but you must enter "type of vehicle" in the same position in the header row.

Using the above matrix computational techniques to produce coherent truth table inside Sumproduct formula.

Using = SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) * (C4: E6))

Truth table logic interdependent (B4: B6 = D10) * (C3: E3 = D9) simply says that Matt elements true when conditions and false otherwise

Sumproduct then takes this and hit by data values and accumulated values for total matching values.

It is important to note that the width and height of the columns and rows parameters must match the width and height data region or # value! Ritornd error.

To understand and explain how this works I will use a simple form with 3 rows and columns, see below

Formula: = SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) * (C4: E6)), shown above consists of 3 regions

(B4: B6 = D10) scope column rows x 1 3

(C3: E3 = D9) x 3 row column group 1

(C4: E6) scope column row x 3 3

Breaking formula to components

= SUMPRODUCT ((B4:B6=D10)*(C3:E3=D9)*(C4:E6))

(B4: B6 = D10) * (C3: E3 = D9) is same as hitting arrays 2, representing regions 2 as shown below

You can see which components are True I put 1 and 0 as false

If history 3/may Excel evaluates to 1, as well as having fruit banana, Excel evaluates to 1.

Where does not meet this standard Excel evaluates to 0

Multiplication 3 x 1 and a 1 × 3 × 3 array 3

The (B4: B6 = D10) * (C3: E3 = D9) part of the equation

Then the data is multiplied by

= SUMPRODUCT ((B4:B6=D10)*(C3:E3=D9)*(C4:E6))

This is the same two arrays double 2 3 x 3, which produces a 3 × 3 below:

ThenSumproduct adds all elements of the array to get the final answer 3of.

Can be "embedded" data area in "logic truth table or as a separate element of Sumproduct.

= SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) *(C4: E6)) f = SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) (C4: E6)) both equal

You can add multiple kritria "OR" using the + operator within a criteria

In scenario 3 above, we collect the number of vehicles Ford, Suzuki sold on May 10.

SUMPRODUCT ((Dates = D24) *((auto = D25) + (auto = E25) + (auto = F25) + (auto = G25) + (auto = H25))* as ardata)

Logic or is added to the criteria using the above criteria operator within parking lot +

Logic is added by using the * between the dates and vehicle standards

You can add greater-than (>), less than (<) etc="" and="" other="" logic="" elements="" to="" the="" queries="" to="" suit="" your="">

Examples are provided below in "example" Excel 2003 is an example of a file.

What do you think the above method?

Let us know in the comments below.

Spread some love
It makes you fabulous!

& Navigation functions

Tags: 2D, and (), array, array formulas "coherent truth table" downloads, Excel recognized matrix arithmetic, matrix, or Microsoft Excel formulas, screencasts, sumproduct

RSS feed for comments on this post. TrackBack URI

View the original article here