Very often, we rely heavily on Microsoft Excel‘s drag-and-expand feature to simplify our work in cutting and pasting formula or numbers such as 1, 2, 3 … 100. Thus, on one fine day, while I was dragging-and-expanding on my Excel financial sheet, I realised that all my figures are wrong and I’m very confident that it has nothing to do with the formula. I was wrong, after trying to figure out. My mathematical formula was right but, my Excel formula was wrong.
Below is a sample of the error that I made, where it suppose to calculate total cost for each day by multiplying the unit cost and the amount of product sold that day:
Before drag-and-expand: It was reflected correct here. Cost(30) = Unit Cost(10) x Sales(3) and the formula is =(A3*B6)
After drag-and-expand: The value for Monday and Tuesday were wrong as the cost should be both 50 and 60 respectively, instead of 75 and 30.
The problem lies in the variables of the formula while we drag-and-expand. The solution is actually pretty simple…
Assuming you are dragging in the horizontal right direction, you will need to stop the formula =(A3*B6) from going to =(B3*C6) and =(C3*D6), instead, it should be =(A3*C6) and =(A3*D6).
In order to do that, you have to add $ sign to the A to prevent it from changing, hence, the formula should be =($A3*C6) for the first column and you do a drag-and-expand.
With the new formula:
You drag-and-expand it.
It’s that simple. 🙂
Best Wordpress Hosting Providers - 2014
All three hosts offer FREE 1-click Wordpress installs making them the best Wordpress hosting providers. For more web hosting reviews be sure to check out AlreadyHosting.com.
Leave a Reply
You must be logged in to post a comment.