Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Link Round Up

Time for a link dump from my browser – clearing out tabs.

2010-07-31 Strobist Shoot Toorbul 162Education

How to Create a Portfolio with Evernote (Education Series) – Evernote is an awesome app – We use it all the time on various android and windows platforms.

Social Marketing and Web Development

Building trust - The Connection Constant

Want to build a online store? Choosing a payment gateway

Facebook – how to convert your personal account into a Facebook Page - for all those people who created a “person” for their business page.

More how to convert Facebook profiles into business pages including tricks and traps

Drupal

Configuring and adding images to Drupal 7 content with Wysiwyg, IMCE and Lightbox2 – very good tutorial

Excel

Probably the best explanation of using INDEX function for non volatile dynamic range creation in Excel

Windows 8

There will be lots coming on Windows 8 on this blog later – for now heres a very good read about how Windows 8 can pool data storage

Business

Realities of Business is a new website by a great friend to promote his new book. Worth a read! Buy it now!

Photography

Two local photographers worth checking out.

Helen Graham

Dale Travers

jethro's picture

Round up of Excel Tips and Hints

As Office 2010 becomes more prevalent in the workplace (most of our clients either have or will be soon updating to it) so the use of the ribbon has become more natural and intuitive for people. It is certainly much more intuitive than the old menu driven interface.

rh2-150x150Play a fun game called Ribbon Hero 2 that allows you to learn the fastest and best ways to do various common tasks in the various Office Apps. My kids are using this to learn the interface and some of these common tasks. I have even learnt some things in the 5 minutes I have spent playing it so far! Download Ribbon Hero 2.

Take the first step in growing your Excel Skills. Microsoft have created a series of learning videos that assist new users to Excel to grow their skills

Learn how to create your own Excel Macros with a free training course. Ideal for the entry level person who wants to explore macro writing for the first time. Note this doesn't create efficient code, but its a start to understanding how the VBA code interacts with Excel.

PowerPivot for Excel - if you have looked or used PowerPivot (See my review of Power Pivot) than this page has some good links for additional resources.

jethro's picture

Tech Talk Christmas Summary

I have had a lot of articles, news and other tech information filling up my browser and its time to de-clutter before Christmas.

ExcelMicrosoft Excel

INDEX -MATCH - a much better lookup function combination than using VLOOKUP or HLOOKUP. Charley Kyd writes a very good instructive post on using this combination. We use this a lot where the failings of VLOOKUP make it impossible to use.

MSDN announces the new developer roadmap for Excel and VBA. Interesting reading and has lots of useful links.

Power Pivot is an amazing AddIn for Excel 2010. I have written about Power Pivot before. Dick Kusleika has written a simple function to determine whether an Excel Workbook contains power pivot data.

OutlookMicrosoft Outlook

Conditional formatting and highlighting your emails based on content, subject, sender etc.

Roy MacLean asks some interesting questions about storing data in Outlook. I love Outlook and think it is a very good tool, however there are lots of limitations with using it. Mind you I don't see any other email client out there that can do everything that Outlook can do or even half as well in most cases. Still Roy has some good points.

jethro's picture

Excel function of the week - AND

The AND function takes each of the conditions inside the brackets and evaluates for their truth, and then multiplies the results together. TRUE = 1 and FALSE = 0. So if any condition is FALSE then the overall statement returns FALSE.

The formula for AND needs to be placed in brackets with each of the conditions separated by a comma. For example =AND(H1<1,G1>1)

In this instance the function will evaluate H1 to see if the value is less than 1. if it is then it will return TRUE or 1 and if not FALSE or 0. Then it will do the same for G1.
The two values are multiplied together and the answer is then either 1 or 0, TRUE or FALSE.

The Excel Help says:

One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

image