Iterations in multiple rows

Aravis

New Member
Joined
Mar 10, 2019
Messages
4
So, it's the first time I'm using iteration in excel. When I first tried it out in just one row it worked fine. The problem is that I need to do it in 23 other rows in the same sheet. The iterations work fine in the first row (row 3), but for the remaining 23 rows I only get the #NUM ! error.

In cell D3 I have:
Code:
=MAX(2.38*(ABS(F3-E3))^0.25;12.1*SQRT($O$19))

In E3 I have:
Code:
=((($O$26/((0.303*EXP(-0.036*B3))+0.028))-(B3-$O$9)+(3.05*(10^(-3))*(5733-(6.99*(B3-$O$9))-$O$23))+(0.42*((B3-$O$9)-(VLOOKUP(A3;'References PMV'!$G$2:$H$26;2;FALSE))))+(1.7*(10^(-5))*B3*(5867-$O$23))+(3.96*(10^(-8))*C3*(((F3+273)^4)-((H3+273)^4))))+(0.0014*B3*34)+(C3*D3*F3))/((0.0014*B3)+(C3*D3))

In F3 I have
Code:
=35.7-(0.028*(B3-$O$9))-((VLOOKUP(A3;'References PMV'!$G$2:$I$26;3;FALSE))*(3.96*(10^(-8))*C3*(((F3+273)^4)-((H3+273)^4))+(C3*D3*(F3-E3))))

So all these cells are refering to each other, and F3 is also refering to itself. I have then "pulled down" the formulas so they are also in row 4-26 in addition to row 3.

I have tried to google for solutions and all I find there is that I should adjust the maximum iterations, but this makes no difference.

Anyone who knows what I'm doing wrong?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,515
Cells that refer to themselves? Isn't that somewhat circular? F3 can't refer to itself within formula... it doesn't know what it's end value is. I don't know how U entered the formula without error? However, if U put a named range in F3 and then referred to that named range within the F3 formula it might work... don't know. Anyways, it sounds like it's just not copying and updating your formula right as U move it to other cells. Take a careful look at what the copied formula cell references are compared to what they should be. HTH. Dave
 

Aravis

New Member
Joined
Mar 10, 2019
Messages
4
Yes, it's circular. I have enabled iterative calculations so that I can use circular references. And the formulas are copying correctly so that is not the problem.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,515
I trialed entering your formulas and there seems to be several errors. U have semicolons in places where commas should be ie….
Code:
=MAX(2.38*(ABS(F3-E3))^0.25;12.1*SQRT($O$19))
Max seems to want a comma between the arguments not a semicolon ie….
Code:
=MAX(2.38*(ABS(F3-E3))^0.25,12.1*SQRT($O$19))
The second formula is similar and I wasn't able to enter the formula at all. I trialled turning on the iterative calculations and still was unable to enter these formulas? Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,173
Members
409,854
Latest member
rickcoba
Top