Formula in cells with changing references

**Idiotfool** · 03-12-2009, 09:23 AM

Hello,

I am running a research experiment in triplicate. Upon generation of data for all three, I create an averaged value of the three. I then take this value and do more calculations with it on a separate sheet. I wanted to know if there was a way in which I could quickly generate formulas based on a changing reference cell.

Example Data:

V1A 28
V2A 26
v3A 30
AvA 28

V1B 20
V2B 22
V3B 18
AvB 20

Vxy represents the value of y sample, x triplicate. V1A being sample A, first triplicate. AvX is the average of the three values.

I want to be able to generate a formula that references AvA which can be applied to the cell immediately below to reference AvB. A simple example would be to square the average.

I can easily do this with a macro, but I wanted to know if this was built into excel at all. I know dragging a formula down makes the reference correspondingly increase, but only by a single amount. I'm not sure if excel has the ability to modify this increment with some sort of modification of the formula, itself.

Thanks

**mdbct** · 03-12-2009, 09:43 AM

You could use something like the following:

Please Login or Register  to view this content.

Where:
$B$5 is the first value you want to reference (AvA in your example)
2 in "ROW()-2" is the row in which the formula resides.
the *5 is the number of rows between values you want to check.

I've attached an example.