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