With all the questions we received about how to consolidate debt we have decided to put together some blogs on the subject. If you are in financial difficulty you may want to consider some these and use spreadsheest to analys the options.

First things first, you need to know what your financial position is. On a spreadsheet itemise all your assets (things you own) and all your debts (clear enough I think). Next you need to create a budget for yourself. How much money do you bring in and how much money do you spend. This will be another blog.

A visitor asked

Adrian,
I’m struggling unsuccessfully @ the following problem, (not winning)

This has to do with comparing 3 sets of columns and the variable, differing answers.

The Columns are: S,T,U are being compared to columns X,Y,Z
S is compared to X
T is compared to Y
U is compared to Z

Ideally, if you can figure this out for me please, it would be neat to get the answers, in A same single cell.
If not possible, I quit……

If  S,T,U, are Less than than X,Y,Z,     (answer expressed as “PPP”).
If S>X=”M”, T<Y=”P”, & U<Z=”P”          (answer is expressed as “MPP”)
If  S>X =”M”, T>Y=”M”, & U<Z=”P”        (answer is expressed as “MMP”)

If the S,T,U are GREATER than X,Y,Z,  (answer expressed as “MMM”.
If  S<X=”P”, T<Y=”P”, & U>Z=”M”        (answer is expressed as “PPM”)
If S<X=”P”, T>Y=”M”, & U>Z=”M”        (answer is expressed as “PMM”)
Lastly, if possible, (coz this is number 7, right?) If either OR, X,Y,Z, is greater than S,T,U by greater than 60%, the answer is expressed as= “X”

Is this too much for Excell?
What’s the 2nd best option?
Hope you can help me.

The question is a bit unclear but from what we see you should be using the AND and OR commands. See how to use them on http://www.auditexcel.co.za/AND_OR.html . Hope this helps, otherwise it is better to send a spreadsheet.

With the draw being completed we now know what the groups are. All the permutations now need to be considered. In light of this we have developed a free excel spreadsheet to try and predict what will happen in South Africa during June.

This is an initial version so please tell us if you find any problems or have suggestions. At the moment it should handle every situation except if we get down to FIFA drawing lots to see who will progress!!

To get a version visit http://www.auditexcel.co.za/world_cup_spreadsheet.html and download the file.

Our initial predictions suggest the following last 16 line up. Run your scenario and tell us what you think the last 16 will look like

A visitor asks

Is it possible to have conditional formatting between sheets/tab within the same workbook?

Yes it can be done but you need to use named ranges to get it right.

If you watch our video clip on Data Validation ( http://www.auditexcel.co.za/DataValidation.html )you will see that Named Ranges are used for certain of the lists. The exact same logic applies with Conditional Formatting. Give the reference cells a name and then within the conditional formatting wizard you refer to the name instead of the cell.

Hope this helps.

A visitor asked

I have refined the spreadsheet you done earlier so that each meal calculates the percentage of fats, carbohydrates, and proteins.  It also does the same for the totals at the end of the day.  But I notice there are green marks inside some of the bottom cells.  Why?

These are some error checks that have been built into Excel. If you hover over the green mark and click on the drop down that appears it will say something like “Inconsistent Formula”. This means that Excel has identified that the cells in this area have a common structure and this cell is different. It does not mean that it is wrong, it is just a warning.

You can switch this feature on and off in the Options sections.

In some instances Excel will try and ‘guess’ that you wanted a column of formula to continue to copy down as you make inputs. It is normally correct but sometimes you may want this functionality to not work.

You can switch it off by going to the options i.e. in 2007 click to windows logo on the top left, choose Excel Options, then Advanced, then Extend Data Range Formats and Formula’s as shown below.

How to switch off automatic format and formula extension

Adrian

A visitor asked

Do you know of a way to delete

1) named ranges and

2) links which link to

defunct/old files or cells.

 

 

When trying to break links, I get no joy at all after replying yes that I want to delete the links

 

The problem will probably be that the links are either in the named ranges or in a graph or picture within the spreadsheet. To delete named ranges you need to go to INSERT and the NAME and then DEFINE (see below). Here you will see a full list of all named ranges and where they look. You will need to go through this list to find which named ranges look outside the spreadsheet.

For the graphs you will need to right click on the graph and check where the source data is.

Hope this helps

A visitor wrote

I am taking a refresher finance course and I am trying to analyze two companies (file of data from googlefinance attached). Here is my problem:

I would like to analyze a couple of companies and I downloaded the financial statement analysis of both companies into Excel. However, when I try to manipulate the date I get a return of: #VALUE.
I found out that this happens because of some programming in the Googlefinance data into Excel.

I would appreciate it if anyone has an experience with this formula if they could tell me how to use it.

It would be great if you could answer it! Thanks for anything you my be able to do. 

You can try the following formula assuming the number you want to manipulate is in cell B4

=VALUE(MID(B4,2,LEN(B4)-2))

The problem is that you have a space before and after each number and excel sees the number as a word and not a number. Hope this helps

This should work.

An unbelievable situation at a client.

2 people looking at the same spreadsheets from the same email on their computers saw different results. After some investigation it was discovered that the one had the Analysis Toolpack switched on and the other off.

In  most situations this would result in a number of error messages which would obviously identify that the Analysis Toolpack should be switched on. However, because the spreadsheet was built with many error checks built in (if a formula is #NA! do something), it actually just changed the results.

Therefor it is important to document your spreadsheet and where necessary specify that the Analysis Toolpack be switched on.