May 2012

A practical way to use the IF function in Excel

Fri, 25/05/2012 - 11:09am — jethro

The IF function is a very useful logical tool. I have written an explanation on the IF Function before so I wont repeat that here. There are a number of other posts also that incorporate the use of the IF Function.

What I want to do is highlight a useful way to use the IF function.

I use it a lot to compare lists of data. today I had been sent some data in a spread sheet that contained email addresses. I had to create a mail out to those email addresses and when i did that I received a number of bounce backs for incorrect email addresses. I then received a second email from the original person containing a revised spread sheet with the corrections to email addresses. given that there were a large number of addresses and only a few changes I didn’t want to painstakingly work my way through the list looking for changes. Instead I copied the new list beside the old list and then wrote an if statement to compare the original with the revised. It is very simple and uses a 1 or 0 result. You could use anything there including TRUE or FALSE or “OK” and “ERROR” etc. Then a simple filter showed me the incorrect ones and the fixes i needed to apply in my mailing list.

Obviously I have obfuscated the email addresses in this example for privacy reasons.

The actual IF statement is very simple. In Cell C1 I wrote =IF(B2=A2,0,1). Filling it down is as simple as double-clicking the right hand bottom corner of the cell.

Then I added a simple filter to hide the correct results and only display those with differences.

I use this technique many times a week in comparing data lists.

Bookmark/Search this post with

Random stuff from my brain

Fri, 18/05/2012 - 9:06am — jethro

Sometimes i need to just dump stuff out of my head. Sometimes my kids just say the funniest random things. Judith went out to my daughter at the clothes line couple of days ago while she was hanging out clothes to get a peg. Jadeen just randomly said “I am a stegosaurus”. That phrase has been bouncing around this house since then amidst lots of laughter.

Our brains are amazingly complex things. Sometimes we just do no appreciate the incredible processing power of simple things like coordinating even our vocal cords, diaphragm, lungs and mouth and tongue just to say a simple word. I was listening to Doctor Karl on Triple J explain how Turrets Syndrome caused people to swear occasionally (its not the most common symptom of Turrets and most people with it don’t do this) and he just reminded me to think about how our brains work.

Last night I dream about alien people and apparently asked out loud for a sword (according to Jude).

Yesterday I was able to run 3 kms with no knee issues – I wish I was back to the running ability I had back in the year 2000. However I’m coming to terms with being 12 years older then I was then and my running is fine as long as I don’t try and run like a 32 year old any more. I did a 5km race a couple of weeks ago and managed a personal best in the last 5 years or so so I am slowly getting it back.

Do you run? if yes where and when, if no – why not? Get into the comments or hit us up on Facebook.

My cycling has improved a lot since I got the new road bike and have been focusing on my hill climbing. My Mountain biking climbing endurance has improved as a result. Unfortunately my Gary Fisher Fat Possum broke in half on my last ride. I am waiting on Trek to honour the lifetime warranty and replace her.

Work has been interesting of late. Since I am no longer employed by IT Integrity and have gone back to being my own boss the pressure on my time has reduced. I am doing far more photography, as that is part of our business and we have had a lot more work in that area. I am doing more of the things I love in IT, web design and Excel work. I am enjoying learning new photography and Photoshop processing techniques. I am really enjoying the sports shooting we do as well.

I bought a new phone. After a disastrous attempt to get an iPhone to work without using iTunes I bought a new Samsung Galaxy IIS and am loving its ease of use, its synchronisation with my Google account and my other android devices, the HTC Desire S and the Motorola Xoom 2 tablet. Its great having all my photos from each device synch seamlessly together with Dropbox, my notes in Evernote all synch across all my devices and it works as a phone out of the box without having to connect it to a computer.

I’ll leave you with a selection of my favourite photos taken recently.

Bookmark/Search this post with

jethro's blog
Login to post comments
Read more
8345 reads

Using VBA Code to Delete Cells Based on the Font Color

Fri, 11/05/2012 - 8:37am — jethro

A while ago a reader named Jeremy sent me a request. Sorry I took so long to respond Jeremy – but I’ve finally done this and here are the results.

How can I delete an entire row based on the font color in column A?

I want do delete every row between A2 and A1000 where the font color is black or automatic. I have tried various alterations of the codes below.

Jeremy supplied several procedures, none of which worked correctly.

I have built a very simple version that does what is needed. I know that this is not good code, but it provides a good starting point. And it works. Anybody who needs to use the guts of it (the selection and deletion based on the font colour) can take that and apply it to whatever ranges they need to.

Here is the scenario.

You have a range of cells that have been coloured with the automatic font colour. (this could be any colour – you would just need to change the colour index in the code.)
You have some items that have been coloured with a different colour.
You need to delete the entire row of any cell that has not been coloured specially, ie all the automatic colour cells.

Bookmark/Search this post with

jethro's blog
Login to post comments
Read more
26040 reads

Excel
VBA

Excel function of the Week - Sum

Fri, 04/05/2012 - 11:29am — jethro

While looking at previous posts to choose a different function for this weeks post I noticed that I have never written a post specifically about the most commonly used function in Excel, the SUM function.

So today’s post remedies that. Note this post is based on Excel 2010. Most of this is still relevant in Excel 2003 and Excel 2007 is largely the same as 2010. The main difference is the ribbon references in Excel 2007/2010 vs the toolbar in 2003.

First the Official Excel Help information.

Description

The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

SUM(number1,[number2],...])
The SUM function syntax has the following arguments:
number1 Required. The first number argument that you want to add.
number2,,... Optional. Number arguments 2 to 255 that you want to add.

Remarks

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.

Bookmark/Search this post with

jethro's blog
Login to post comments
Read more
22125 reads

Site Construction and Maintenance by Jethro Consultants

Gidday
Glad to have you stop in.
I write about my family, the internet, technology and gadgets, productivity with Microsoft Office applications Excel Tips and more.