Help with circular reference problem

[bradyj7]

Hi there,

I have three columns Time (t) Charge (Q) and Current (I).

The Time column contains second values 1,2,3 etc.

Q is defined as I * t.

I is defined as Q * 2.

So for first time step I is given a value so Q can can be calculated. Then from Q, I can be calculated. Now for the next time step I want the new value for I to be used to calculate Q and then I again and so on

t	                 Q	                            I	
1	1* 0.01(starting value for I)	         0.01*2	
2	This should be 0.02 *2	        This should be 0.04*2	
3			.....                      ......
4			.....                      ......

Would anyone know how to code this?

Thanks in advance

John

 

[GlennUK]

Surely it is not a circular reference ... you are wanting to reference the I from the previous step for each new Q. So that would mean it would look like this:

Excel Workbook
	A	B	C	D
1			initial value ===>	0.01
2	t	Q	I
3	1	0.01	0.02
4	2	0.04	0.08
5	3	0.24	0.48
6	4	1.92	3.84
7	5	19.2	38.4
8	6	230.4	460.8
9	7	3225.6	6451.2
Sheet3

 

[JackDanIce]

Hi,

Assuming t is in column A with all the number of t values required, Q is in column B and I in column C then, with your value of t=1 in cell A2, then something like:

Sub MyIQT()
Dim x As Long, I As Double
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
I = InputBox("Enter a starting value for I: ")
Range("B2") = Range("A2") * I
Range("C2") = Range("B2") * 2
x = Range("A" & Rows.Count).End(xlUp).Row
With Range("B3:B" & x)
    .FormulaR1C1 = "=RC[-1]*R[-1]C[-1]"
    .FillDown
End With
With Range("C3:C" & x)
    .FormulaR1C1 = "=RC[-1]*2"
    .FillDown
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
End With
End Sub

 

[bradyj7]

Hi,

Thanks for the replies. I'm trying to implement this into a larger macro that I have to run on a lot of files. I couldn't not code it because the Q column is before the I column and so it wouldn't update the new I value for each time step. I'll try the code above and let you know how I get on.

Thank you