Excell Formula Help

[munley]

I am using this formula to calculate heating and cooling degree days. I noticed that that the other days the average temperature was 61F and it calculated 4.5. It should be only 4 heating days because the average temperature is below 65F. What is wrong?

=IF(AND(D13<65,D13<>0),65-D13,0)

[JohnTopley]

I suspect D13 isn't 61 but 60.5 and formatted as number with 0 decimals ???

[MrShorty]

Probably going to be difficult to diagnose without seeing exactly what is in D13. If the formula results in 4.5, that suggests that D13 contains the value 60.5 (which would display as 61 to the nearest integer). As near as I can tell, everything is working exactly as it should, so what exactly is the problem? Do you need to include a ROUND() function somewhere so that it sees the value of D13 as 61 instead of 60.5? Should you put that ROUND() function into D13, or include in the function you show here? I would guess that you need to include a ROUND() function somewhere to get the logic you desire: https://support.office.com/en-us/art...1-b3e7f61a213c

[munley]

I am using this formula to calculate e heating degree days. I noticed the other day that the average temperature was 61F. It should calculate 4. Instead it calculated 4.5. What is wrong with this formula?

=IF(AND(D13<65,D13<>0),65-D13,0)

[JohnTopley]

See replies.

[munley]

Yes. This is the formula that gives me 61. The formula was initially displayed as 60.1. I formatted the number to 0 dismals. This is the formula that gives me 61. =IF(OR(B14>0,C14>0),AVERAGE(B14:C14)," "

[MrShorty]

I see nothing in this formula that forces the average of two (real?) numbers to be an integer. I cannot even tell if B14 or C14 are constrained to be integers (an average of 60.1 would suggest that they are not integers). As near as I can tell, everything is calculating correctly, what do you want to happen differently? I would guess that you want to force the calculation to be an integer at some point along the way -- do you want to force B14 and C14 to be integers (both odd or both even so the average will be an integer)? Do you want to allow B14 and C14 to be reals, but constrain the average to be an integers [=ROUND(AVERAGE(B14:C14),0)]? Or do you want to have the formula in the OP convert the average to an integer =65-ROUND(D13,0)?

[munley]

The numbers in B14 and c14 are the actual high and low temperature of the day. They could be even or odd numbers. The highs and low for that day are 69 and 62. That is 60.5. I want it rounded to 61 then, I want the degree days to be 4. Is there any way that I can upload the spread sheet?

[MrShorty]

I know that some people have trouble with the file uploader here. I don't, and I click -> go advanced -> find the "manage attachments" link below the edit window -> use the popup window to upload the files.

That is 60.5. I want it rounded to 61 then,

Then I would suggest you nest your AVERAGE() function in D13 inside of a ROUND() function, like this: ROUND(AVERAGE(B14:C14),0)

[munley]

I tried can't upload. I tried in IE11 and google. No pop up.

[munley]

Oh, I guess I uploaded. It worked.

[JohnTopley]

In D6

=IFERROR(IF(OR(B6>0,C6>0),ROUND(AVERAGE(B6:C6),0)," "),"")

in F6

=IFERROR(IF(AND(D6<65,D6<>0),65-D6,0),"")

You want to change the IFERROR(........,"") to IFERROR(.......,0)

[munley]

That is the exact formula that I can cut and paste into D6?

[JohnTopley]

Yes: and for F6

[munley]

What is the formula to but into F6 so I can cut and paste? I copied and paste what you gave me and I got the same results. Where do I put the (.......,0)?

[JohnTopley]

in F6

=IFERROR(IF(AND(D6<65,D6<>0),65-D6,0),0)

[munley]

I tried that. It does not work. I just inserts the formula like text.

[Tony Valko]

That usually means the cell is formatted as text.

Change the cell format to General then re-enter the formula.

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)