Solve for Exponential Equation giving #NUM! error

[Adecypher]

Hi there,

I am trying to solve the following listed equation for the value of B1 using the Excel Solver and I am getting #NUM! error:

Please guide me on how to avoid the #NUM! error and solve for B1.

Thanks

 

[BadgerRoad]

It's unclear to me what you are trying to do and why Solver is involved. The formula in B2 is a function of B1, B5, B6, B7 and B8 all of which are defined. When you put 1 in B1 your formula tries to raise e to the 12609th power which is too big a number for excel to handle. That's where the NUM error comes from.
If I've misinterpreted what you are trying to do please clarify.

 

[Eric W]

First, check to see if your formula is correct. It's easy to miss something converting a long formula to Excel functions. Try a few known values to make sure it's right.

Given the formula as presented, like BadgerRoad said, you're going to get a huge number with the given values. Try changing the B1 value to 100, and it should calculate. Then I'd suggest using Goal Seek instead of Solver, it's all you need for this problem. So click on B2, then Goal Seek from the Data tab, then Set B2 to 200 (or whatever value you're looking for), by changing B1. I looked for a value of 200 and got this as a result:

Book1
	A	B
1	x=	298.513182
2	f(x)=	199.999988
3
4
5	Φ	1.09
6	k	0.00008617
7	T2	316.09
8	T3	302.59
Sheet11

Cell Formulas
Range	Formula
B2	B2	=15.76*EXP((B$5/B$6)*((1/B1)-(1/B7)))+64.24*EXP((B$5/B$6)*((1/B1)-(1/B8)))-80

 

[Adecypher]

@BadgerRoad,
First of thanks for the reply and yes it is my bad I did not explain the objective which is to solve the equation for T1 which is a term in the Arrhenius Equation called "Normal Service Temperature". As far as the overflow issue due to the computation of EXP in the formula; I guess the Solver method then is not appropriate here since I cannot get rid of the terms raised to the EXP.

@Eric W
Thanks for your reply, as I responded above unfortunately I cannot change the values (please see snapshot of the spreadsheet below for more info).

 

[Eric W]

I'm struggling to understand the formula, in particular the T values. The written formula has T and T1, but no T2 or T3 in it, but in the Where: section, there is T1, T2, and T3, but no T. Moreover, I can't reconcile either version with your Excel formula.

As far as changing the values, I understand that some values are constants, and others are fixed parameters of the given problem. However, the way that Solver and Goal Seek both work, sometimes you need to start with a "guess" for the value you're looking for.

 

[Adecypher]

I'm struggling to understand the formula, in particular the T values. The written formula has T and T1, but no T2 or T3 in it, but in the Where: section, there is T1, T2, and T3, but no T. Moreover, I can't reconcile either version with your Excel formula.

As far as changing the values, I understand that some values are constants, and others are fixed parameters of the given problem. However, the way that Solver and Goal Seek both work, sometimes you need to start with a "guess" for the value you're looking for.

Please disregard the value of T2 and T3; only need to solve for T1. I can move forward with either Solver or Goal Seek but first I need to know how to fix the #NUM! error, what I should change?

 

[Adecypher]

@Eric W
I was able to resolve the issue of #NUM! error by changing the values in cell B1 (Please see below):