delay calculation (Excel03)

Topic

New Reply

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

Reply | Quote

Replies

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:

=IF(ISBLANK(B2),"",B2-A2)

This formula returns an empty string if actual hours haven’t been entered yet, and the difference between actual and budgeted hours otherwise.

Reply | Quote

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

Reply | Quote

You could also try testing for 0 instead of blank.

=IF(B2=0,"",B2-A2)

B2=0 will evaluate to TRUE if the value is 0 but also if B2 is blank.

Reply | Quote

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

Reply | Quote

Does this formula do what you want?


=IF(AN15"",AM15/AL15,"")


Reply | Quote

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

Reply | Quote

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

Reply | Quote