# Dynamic Formula

#### lmejias

##### New Member
I have a data set with a dynamic range and can write a formula to fill down. but o would like the r1c1 callouts to use a dynamic range instead of a preset. Help please!!

here is my current formula (10 columns of data)

normally I would:

Dim endRow As Long
endRow = Cells(Rows.Count, "A").End(xlUp).Row

'set formula
Range("A1").End(xlToRight).Offset(1, -2).FormulaR1C1 = _
"=IF(OR(MEDIAN(RC[-10]:RC[-1])*1.05<RC[1],MEDIAN(RC[-10]:RC[-1])*0.95>RC[1]),""Check for Potential Outliers - "" & COUNTIF(RC[-10]:RC[-1],"">""&1.05*MEDIAN(RC[-10]:RC[-1]))+COUNTIF(RC[-10]:RC[-1],""<""&0.95*MEDIAN(RC[-10]:RC[-1]))-COUNTIF(RC[-10]:RC[-1], ""=0""),"""")"

'then fill down
Range("x").AutoFill Destination:=Range("x2:x" & endRow)

but what if I have 5 or 50? how do I change RC[-10]:RC[-1] to value#1 to last value in a row and then fill formula down a column to last row.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Replies
4
Views
591
Replies
3
Views
176
Replies
3
Views
149
Replies
4
Views
829
Replies
6
Views
179

1,203,068
Messages
6,053,342
Members
444,654
Latest member
Rich Cohen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back