Ever have an Excel column or row that is designed to continually expand with additional data?
Do you have calculations – may a sum or average or whatever – from the set of data?
And when the amount of data increases what do you do with your formulas?
If you’re changing the cell range each time then I know that you’re frustrated.
Or maybe you wrote the formula by using the address of the first and last cells in the row or column?
And while once you know the address of the last cell it certainly makes for some long formula strings – which can be difficult to edit and handle later on.
So the question must be asked … is there a simpler way? Maybe something that doesn’t require continual formula editing or obnoxiously long formulas?
A simple way you ask – no problem.
When writing a formula that should use the data in an entire column/row, use its letter/number as both the beginning and ending locations.
Sounds confusing I know but wait – a quick example will clarify things immensely.
Let’s say that I need the average of all data in column A. In the location I want the average placed use this:
Need a calculation from an entire row? =AVERAGE(1:1) will average all data in row one.
Well, you get the idea…
I’m not sure we could write a cleaner formula for any calculation and the happy news is that this one replaces others that had the potential to be messy and annoying!