Explaining specific formualae to co-workers

[jdkshepard]

I recently was assigned a task of deciphering a curiously large excel sheet. We always deal with sheets that fit within A-z columns this one goes to CH -_-


It has a lot of If, then statements and basically it was my job to figure out what each formula did and write it in a way to be able to be understood visually and textually.


Any sugestions?


Thanks,

 

[mrzoogle]

Hi jdkshepard,


In this situation what I would do is to count exactly how many formulae are in your spreadsheet and how many different functions (formulae types) are being used. After that, especially with the "if then" mega formulae I usually find it easier to copy the whole formulae in the new cell or tab and remove the "=" sign so that it is no longer formulae and then break it down into different rows. So that you can trace it back and understand what each part is doing exactly.

Hope this helps.


Kind Regards,

Z.

 

[Hui]

Jdkshepard


Spreadsheets nearly always have 3 or 4 similar areas

1. Input area

2. Data Storage area

3. Calculations areas

4. Reporting areas


Look for Tab Colors/Names as indicators of the above areas

Look for Titles on worksheets as indicators of the above areas


Hopefully they have used some sort of Cell Formatting to identify areas that fall into the above categories


Start at either end Inputs or Reports and work towards the other one

You may want to color cells as you go to keep track of where you've been


I also use John Walkenbach's PUP tools which has a great Auditing Tool

It literally makes a Map of a Worksheet with a Display of the cell type Text, Number, Formula and can show how complex a formula is. Areas of fornmulas with similar complexity nearly always do the same thing so you can follow through the logic quickly


PUP is available at: http://spreadsheetpage.com/index.php/pupv7/home/