Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Troubleshooting "Fill Down/Right" with a formula
Does anyone know if it is possible to "Fill Down" or "Fill Right" with a cell
formula without changing *all* of the cell references in the formula? For example, I want to compute a set of averages by dividing the sum of an array by a the contents of a cell containing the total number of values. My original cell formula reads "=sum ((A1:A10)/A11))". Is there some way to allow the "A1:A11" reference to change to "B1:B10" with a Fill Right command, but keep the "A11" reference constant (i.e., not changing to "B11"). I often run into wanting to compute values that include "constants" obtained from other calculations. Is there any easy fix to this, short of a cumbersome search-replace routine? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Troubleshooting "Fill Down/Right" with a formula
=sum(A1:A10)/$A$11
The $ signs indicate an absolute reference. References without dollar signs are relative references. So if you fill the above formula down, A1:A10 will change but A11 will remain static. Dave -- Brevity is the soul of wit. "Trey Buchanan, Ph.D." wrote: Does anyone know if it is possible to "Fill Down" or "Fill Right" with a cell formula without changing *all* of the cell references in the formula? For example, I want to compute a set of averages by dividing the sum of an array by a the contents of a cell containing the total number of values. My original cell formula reads "=sum ((A1:A10)/A11))". Is there some way to allow the "A1:A11" reference to change to "B1:B10" with a Fill Right command, but keep the "A11" reference constant (i.e., not changing to "B11"). I often run into wanting to compute values that include "constants" obtained from other calculations. Is there any easy fix to this, short of a cumbersome search-replace routine? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Troubleshooting "Fill Down/Right" with a formula
Dave:
Thanks TONS for that tip -- it will save me lots of time and effort! Have a great day, Trey "Dave F" wrote: =sum(A1:A10)/$A$11 The $ signs indicate an absolute reference. References without dollar signs are relative references. So if you fill the above formula down, A1:A10 will change but A11 will remain static. Dave -- Brevity is the soul of wit. "Trey Buchanan, Ph.D." wrote: Does anyone know if it is possible to "Fill Down" or "Fill Right" with a cell formula without changing *all* of the cell references in the formula? For example, I want to compute a set of averages by dividing the sum of an array by a the contents of a cell containing the total number of values. My original cell formula reads "=sum ((A1:A10)/A11))". Is there some way to allow the "A1:A11" reference to change to "B1:B10" with a Fill Right command, but keep the "A11" reference constant (i.e., not changing to "B11"). I often run into wanting to compute values that include "constants" obtained from other calculations. Is there any easy fix to this, short of a cumbersome search-replace routine? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Troubleshooting my IF formula | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |