Calculating Vapor Pressure Deficit in Excel (Problem with complex formula)

**pinotblogger** · 12-23-2008, 01:26 PM

Hello excel geniuses!

I've run into a formula that I can't seem to get working correctly in Excel and I'm about at the end of my rope.

The formula I'm interested in is the first calculation for estimation of Vapor Pressure Deficit (important in Viticulture and botany in general).

The specific formula can be found under Step 2a in the attachment below. It's also located 3/4 of the way down the page here.

In the code below is the formula I've input into excel. C2 references a field that holds Ambient Temp in degrees F (in the formula it is converted to Kelvin). In the attachment below they give an example using 50F which gives a result of .178 psi.

Please Login or Register  to view this content.

My formula keeps generating a result of 0, and only 0 unfortunately. I've checked and compared the two formulas multiple times but can't find a typing error. I'm left thinking that my nesting of parenthesis are off or that I'm not formating the exp() or ln() functions correctly somehow.

I really appreciate any help or advice anyone can give!

Josh

**shg** · 12-23-2008, 01:30 PM

Welcome to the forum.

In order to avoid having to answer the same questions every day, we require that posters compose thread titles that make the forum usefully searchable. Your title doesn't advance that goal.

Please take a few minutes to read the Forum Rules about thread titles, and then edit yours to make it descriptive of your problem.

**JBeaucaire** · 12-23-2008, 01:32 PM

If the Formula Auditing Toolbar isn't visible, turn it on. Then highlight your cell with that formula in it and click on the Evaluate Formula icon.

In there, you can step through your formula on click at a time and spot where the "Zero" is occurring.

**pinotblogger** · 12-23-2008, 01:36 PM

Shg,

My apologies. I've changed the title.

JBeaucaire,

I'll give the auditor another try to see if I can figure it out. When I ran it last (2 days ago) I was getting a different error, so perhaps it will help me nail down the 0 problem.

Thanks for the advice!

**pinotblogger** · 12-23-2008, 01:45 PM

Formula auditor doesn't help unfortunately. I've uploaded the last two steps as attachments. As you can see there is no zero error until Excel attempts to evaluate exp(-18178.2189877272).

This evaluates to 0 for as many decimals as I care to try.

This is a proven formula, so I know the problem is on my end.

**JBeaucaire** · 12-23-2008, 01:55 PM

Well, just typing in the formula =EXP(-18178) gets you zero. When I use any number less than EXP(-708) it results in a 0, so your formula has to create a result less than for EXP to do anything with it.

**shg** · 12-23-2008, 01:56 PM

The signs of two of your constants (D and F) are wrong:

Please Login or Register  to view this content.

BTW, temp is Rankine, not Kelvins.

**pinotblogger** · 12-23-2008, 02:13 PM

Fantastic!

It seems my nesting is causing an error (besides the signs of the constants - doh!) since when I change the signs in my equation I get a #NUM error.

When I calculate it the way you've shown things work out just fine.

Thank you shg!

BTW, Do you like Extra Virgin Olive oil? I'd love to send you a bottle for the help. If you're interested, email is josh at pinotblogger.com.

Thanks again!

PS You're right about Rankine as well. I was working on a metric version of the formula earlier and well, yet another mistake.

**shg** · 12-23-2008, 02:19 PM

Love EVOO.

Would you please mark the thread as solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

**ruswin** · 02-26-2011, 01:02 PM

It seems that this formula was completed successfully, but is incomplete here on this post. Is there a final version of the excel formula / file that I can get? If anyone can help, please respond to EmailDeleted@mods.com. THANKS!

**shg** · 02-26-2011, 01:58 PM

The complete solution is shown in post#7. If you have questions, please start a new thread and add a link to this one if appropriate.

**mooselodge** · 03-25-2014, 01:02 PM

in step 7 what is the LN? all of the other parts of the formula are referenced in the standard numbers. Also if I am working in Celsius do I need to convert to Rankine?

**MrShorty** · 03-25-2014, 01:25 PM

@mooselodge: This forum is pretty strict about starting your own thread, rather than posting your question in an old thread.

LN is natural logarithm. Choice of temperature units will be specific to the equation/coefficients given (this particular example is specific to T in Rankine). If you need more help than that, please start your own thread, including a link to this thread if you feel it is particularly important to the posting of your own question.