Thread: delay calculation (Excel03)

1. delay calculation (Excel03)

I often create a database for a period of time in the future where I have one side of the information for example budgeted hours. The actual hours obviously can't be entered until after the event. Then there are formulas to get results. What I want is to prevent the formula from calculating until both the budgeted and actual hours are entered. How do I do that? Thank you. Merry Christmas and a Happy New Year. Fay

2. Re: delay calculation (Excel03)

You can use ISBLANK combined with IF to make a formula return an empty string if one or more of the contributing cells are blank. For example, with budgeted hours in A2 and actual hours in B2:
<code>
=IF(ISBLANK(B2),"",B2-A2)
</code>
This formula returns an empty string if actual hours haven't been entered yet, and the difference between actual and budgeted hours otherwise.

3. Re: delay calculation (Excel03)

Hi Hans! I think this will work, except that there are zeros in the Actual column. Let me explain that I have rows where the managers will input the individual's hours. Those columns are automatically summed. Then the formulas are placed for columns a zero is returned since there is currently no data. That result is linked to where I put the ISBlank equation you sent. I was thinking that I could use the ISBlank to prevent getting the zero, but that will not work. It is a range from C4:C70. I was planning for there to be numbers in only those rows where the person worked. So that would leave a lot of blank cells. Wouldn't that confuse the ISBlank statement?

Fay

4. Re: delay calculation (Excel03)

You could also try testing for 0 instead of blank.
<code>
=IF(B2=0,"",B2-A2)
</code>
B2=0 will evaluate to TRUE if the value is 0 but also if B2 is blank.

5. Re: delay calculation (Excel03)

One correct leads to another. I got the above working. What now is problematic is the %change column in the attached spreadsheet.

What I don't want to see is the 0% with green formatting in the bottom of the AO column. I have been fussing at this and tried this formula in column AO15
=IF(AN15<>" ",AM15/AL15," ")

Thanks for your time, expertise, and help.

Fay

6. Re: delay calculation (Excel03)

Does this formula do what you want?

<code>
=IF(AN15<>"",AM15/AL15,"")
</code>

7. Re: delay calculation (Excel03)

You should have used empty strings <code>""</code> instead of strings consisting of a single space <code>" "</code>. But this will cause the blanks to be colored red. You can suppress this by using another condition in Conditional Formatting. See attached.

8. Re: delay calculation (Excel03)

Thank you gentleman. I thought I had delete the space at one time or the other. But I think I forgot to do both.

Have a happy holiday season. Fay

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•