May 03

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:

Excel Drag-And-Expand Error 1
Before drag-and-expand: It was reflected correct here. Cost(30) = Unit Cost(10) x Sales(3) and the formula is =(A3*B6)

Excel Drag-And-Expand Error 2
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:

Excel Drag-And-Expand Done 1

You drag-and-expand it.

Excel Drag-And-Expand Done 2

It’s that simple. 🙂

written by mangoorange


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.

 #1
#2 
 #3
#1 - Bluehost 
Bluehost Review
#2 - iPage 
iPage Review
#3 - HostMetro 
HostMetro Review


Leave a Reply

You must be logged in to post a comment.