Help creating a formula

[Lithium]

Excel 2000 Novice My first post.

I am trying to create a simple SS which calculates heating degree days and
cooling degree days.

Column headings are High, Low, Average, Heating Degree Days and Cooling
Degree Days.

If the average is under 65° (=65-AVG) then the number should be put in HDD
column, If over 65° (a negative number, or =AVG-65) the number should be put
in CDD column.

I'm not proficient enough to create a formula to put the number in the
appropriate column and leave the other column blank.

Can someone please give me some help with this?

 

[Mike H]

Hi,

I've assumed a daya layout of

Col A Col B Col C Col D Col E
High Low Ave HDD CDD

So the Col D (HDD) formula is
=IF(AND(C2<65,C2<>""),C2,"")

An in CDD
=IF(C2>=65,C2,"")

Note an average of exactly 65 has to go in one or the other column and I've
guessed at CDD. Drag the formula down as required

Mike

 

[FSt1]

hi
first, formula return values to the cell in which the reside. they cann't
put values into other cells.
so, will will need a fomurla for each calculation , one in the HDD column
and one in the CDD columns
lets say that high is column A, low in column B, Average is column C, HDD is
column D and CDD is column E.
assuming that the AVG is in column C, in D1 enter...
=if(C2<65,65-C2,"")
or if C2(AVG) is less than 65 then 65-AVG else leave it blank.
in E2 enter...
=if(C2>65,C2-65,"")
or if C2 is greater than 65 the AVG-65 esle leave it blank.

regards
FSt1

 

[Lithium]

FSt1: Thank you for the quick response. Your formulas work.

Mike_H: Your formulas gave me the AVG in the HDD and CDD columns--maybe
something I didn't do right.

 

[FSt1]

hi
thanks for the feed back. glad to help.
i do how ever need a spell check/grammer secretary. or take a remedial
typing course.
sigh.

Regards
FSt1

 

[Lithium]

Just a quick followup...

The formulas are working except I can't seem to get the AVG, HDD and CDD
columns to be blank if the High and Low values ="".

I managed to get AVG to be blank by using IF High="" but couldn't get both
High *and* Low to work. Similar problems with HDD and CDD.

------------

 

[Chris Bode via OfficeKB.com]

You can follow following easy steps
A B C D E
High Low Avg HDD CDD

1.Select D2 and enter following formula
=IF(C2<62,C2,"")
2.Select E2 and enter following formula
=IF(C2>62,C2,"")

Hope you get it

Have a nice time

Chris

 

[FSt1]

hi
the formula i gave didn't have anything to do with high and low(column A and
B)
how did that come into play. are you using formulas not mentioned in your
previous posts??????

Regards
FSt1

 

[Lithium]

Now I'm really confused. Let me give some details.

C6 is the High temperature (no formula)
D6 is the Low temperature (no formula)
E6 is the Average Formula: =(C6+D6)/2 . This shows as 0 if C6 and D6 are
blank.
F6 is HDD Formula: =IF(E6<65,65-E6,"-") . This shows as 65 if C6 and D6
are blank.
G6 is CDD Formula: =IF(E6>65,E6-65,"-") . This shows as - if C6 and D6 are
blank.

If C6 _or_ D6 is blank then E6, F6 and G6 _should_ also be blank.

As I have said, if I have numbers for High and Low everything works right.
It's just that when the High and Low have not yet been entered, the Average,
Heating Degree Days and Cooling Degree Days should be blank (or have just a
dash).

TIA

 

[David Biddulph]

=IF(COUNT(C66)=2,(C6+D6)/2,"")
=IF(COUNT(C66)=2,IF(E6<65,65-E6,"-"),"")
=IF(COUNT(C66)=2,IF(E6>65,E6-65,"-"),"")

Note, as Mike H pointed out earlier in the thread, that you didn't specify
what to do if E6 was exactly 65.

 

[Lithium]

Perfect!

Being a novice, I had to look up Count since I was not familiar with it.

When AVG is 65 it displays just fine.