Recently I’ve been doing a lot of working with Excel and I’ve really pushed myself to solve some problems that have bugged me for a number of years.
I wanted a way to convert between column letters and a numerical value. As you may be aware you can reference a cell via its row number and the column letter, but you can also reference it via row number and column number e.g. A=1, B=2 and C=3
It’s useful sometimes (when programming especially) to switch between the methods you use. Not wanting to type out (in this case) 153 strings e.g. A,B,C,D ... EZ, I found the following formula which can be copied into A1 and downwards and it will give you the running values
=SUBSTITUTE(ADDRESS(1, ROW(), 4), "1", "")
Very useful.
The second formula (actually function) I’ve come across is
=SUMIF(range,criteria,sum_range)
Usage: Range is the range of cells you want to query on e.g. Place, Sex, Unit of measure, Criteria is the what records you want it to match on e.g. London, Male, KG. Sum Range is the range of cells you want it to total up.
Very simple, yet very powerful
