Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula for monthly data (9.0/2000)

    In a workbook I have worksheet 1 with columns containing the alloted budget (in hours) per task for the duration of the program, the % complete of that budget through weekending 3/12/04 (we status weekly), % complete through weekending 3/19/04, cummulative hours spent from the begining of the program (Aug.) to 3/12 or 3/19 (depending if the person's updated status this week yet or not), and the current hours spend for weekending 3/19 . On worksheet 2, I have columns containing the % complete history for wknd 3/12, 3/5, and 2/27 (we just started this wkly statusing deal). A manager wants to see the hours per task spent cummulativly for the month, not just entire program thus far. I'm trying to build a formula that takes ((ws2 column 3/5 (% completes) - ws2 column 2/27 (% completes) )and multiply that difference by ws1 column alloted budet) to get the March cummulative thru weekending 3/5. I also want it to return nothing for tasks with blank % completes instead of #value. Then for weekending 3/12, I have to again (((subtract % complete 3/12 from % complete 3/5 )and multiply that difference by the total budget )and add it to the March cum. thru 3/5 value) I came up with. However when I do the formula, some tasks it seems to work, and others don't look that great... can anyone help me? And I'm having trouble returning a blank instead of #value.

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

    Re: formula for monthly data (9.0/2000)

    The seemingly empty cells, such as Sheet2!A7:C7, are not all really empty. This can happen if the data have been imported from another application. You can use the following macro to clear the empty-seeming cells:

    Sub ClearEmptyCells()
    Dim aCell As Range
    On Error GoTo TheEnd
    Application.ScreenUpdating = False
    For Each aCell In Selection
    If Trim$(aCell.Value) = "" Then aCell.ClearContents
    Next
    TheEnd:
    Application.ScreenUpdating = True
    End Sub

    Select the range (A1:C213) before running this macro.

    Put the following formula in F3:

    =IF(ISBLANK(B3),"",(B3-C3)*Sheet1!A3)

    and this one in G3:

    =IF(ISBLANK(A3),"",(A3-C3)*Sheet1!A3)

    Then select F3:G3 and fill down to row 213.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula for monthly data (9.0/2000)

    A non macro approach to clearing the cells is to highlight the region A3:C213
    Edit - goto - <special>
    Choose "Constants" and then uncheck all the checks but "Text"
    <ok>
    [All the "text" in selection is selected
    Press <delete>

    Steve

Posting Permissions

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