Excel question?

Question

i have a worksheet with numbers.
cell a7 has a formular =sum(a2:a6)
then b7 has a formular =sum(b2:b6)
ok, here is the problem
i want cell c2 to be =b2/a7....which works ok
when i try to drag it down so that the answers comeup for the auto matically it gives errors.
div/0
cos cell c3 formular changes to =b3/a8
so could someone how i can make that a7 to remain constant when i drag down, please.

Answer 1

If you want a cell reference to stay constant (it's called "absolute" in spreadsheet-speak), during the cell edit, place the cursor in the reference and press the "f4" key, or put a "$" in front of the col/row like =b2/$a$7

Answer 2

I don't think anyone else understood the issue. Try this formula in C2 instead:

=if(iserror(b2/a7),"",b2/a7)

That'll leave C2 blank if you have a zero in b2 or a7. There are other ways to handle errors. Try my extreme Excel tutorial; specifically "error handling" toward the end.

Answer 3

Set C2= B2/A$7

You don't need to put a $ in front of the A (although it will still work in this case) since you don't need to force the column to remain the same when you're going up and down the column.

Answer 4

Set c2 to be =b2/$a$7 - the dollars essentially mean "don't change automatically when you drag me". Technically they're called absolute references (rather than relative references).