Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delay calculation (Excel03)

    Does this formula do what you want?

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •