Cannot solve this #VALUE! error. Please help

**dbryan00** · 02-23-2013, 11:54 PM

Hello all and greetings as I'm new. I have a question concerning conditional formulas in my excel program. Its supposed to be a fairly simple program to output a heating degree day(HDD) value (=65-average temperature for that day) if a day's temperature is below 65 degrees and a zero for the adjacent cell for the cooling degree day value and vice versa (meaning output a cooling degree day (CDD) if the day's average is greater than 65 degrees. If the cell designating average temp is blank then both HDD and CDD cells should be blank (""). As per my attached excel sheet I keep getting a #VALUE! error for CDD cells when the average is blank. The formula in question is over the G column =IF(E12>$A$1,E12-$A$1,IF(E12="","",0)). These conditional formulas are logic based, and my formulas make logical sense to me. I've read mine over 1000 times literally before I decided to post my question here and thats the most frustrating part. Please help. Thanks *100.

Degree Days.xlsx

**benishiryo** · 02-24-2013, 12:10 AM

hi dbryan00, welcome to the forum. i dont really get it why you changed your formula in G25. the reason you are getting VALUE error is because you cannot take ""(E23) minus 65 (A1). so maybe:
=IF(E12="","",IF(E12>$A$1,E12-$A$1,0))

if you need a positive result no matter which is bigger, then:
=IF(E12="","",ABS(E12-$A$1))
but i dont know how the 0 comes into play.

a good way of helping us understand is to put in the desired results you want to see.

**Ron Coderre** · 02-24-2013, 12:10 AM

Using your posted workbook, these formulas return the results you're asking for

Please Login or Register  to view this content.

Copy those formulas down as far as you need.

Is that something you can work with?

**haripopuri** · 02-24-2013, 12:23 AM

Hi dbryan,

Cell E23 on which G23 formula is based is having a value "" (not blank or zero). Excel takes this "" as text hence is unable to perform calculation with this. Consider using 0 instead of "".

**dbryan00** · 02-24-2013, 02:46 AM

Thank you all for your quick replies. I can happily say that my problem is solved. I ended up using benishiryo suggestion but adding >= just to cover all my bases =IF(E12="","",IF(E12>=$A$1,E12-$A$1,0)) for CDD column which returns an empty cell for an empty cell while still applying my formula. Ron your suggestion was 100% valid and working as well but I just didn't want any hint of an error generation if it could be avoided. Thank you folks! Rep left