Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Average with variable, and days to average

    I have a list of start & finish dates, time intervals that have several categories, fine and regular. Each time interval is associated with a category [only one]. I need to find the average # of days or time interval, for each category.
    I also need to know the "time remaining to" the average # of days for the specific category associated with the last entry that has a start date but no finish date yet. In other words, if the last entry is "regular" and the average days for "regular" is 15 days and the average days for "fine" is 8 days and the start date is 10 days ago, the return should be 5 days.
    Attached example:
    G3 would be the avg days for those categorized in column D as "regular".
    G4 would be the avg days for those categorized in column D as "fine".
    I4 would be the days remaining to "fine" average [G4] if last entry [A11] is "fine".
    K4 would be the days remaining to "regular" average [G3] if last entry [A11] is "regular"
    In my example, the category is "regular, so I need to return the number of days from 3/13 to "today" minus the return in G3 or the "regular" average.

    Can you show me how to do this with column "C" [# of days calculated] and without column "C", without the # of days calculated already.

    Thanks.
    Attached Files Attached Files

  2. #2
    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
    Not sure I completely understand, but I will give it a try.

    In G3, the average of the regular is given by (change range as desired) the array formula (confirm with ctrl-shift-enter):
    =AVERAGE(IF(($D$3:$D$100="regular")*ISNUMBER($C$3: $C$100),$C$3:$C$100))

    In G4, the average of the fine is given by (change range as desired) the array formula (confirm with ctrl-shift-enter):
    =AVERAGE(IF(($D$3:$D$100="fine")*ISNUMBER($C$3:$C$ 100),$C$3:$C$100))

    In I4, the difference days needed to give the average is (it will be null if the last value is "regular")
    =IF(VLOOKUP("zzzzz",D:d,1)="fine",G4+INDEX(A:A,MAT CH("zzzzz",D:d))-TODAY(),"")

    In K4, the difference days needed to give the average is (it will be null if the last value is "fine"):
    =IF(VLOOKUP("zzzzz",D:d,1)="regular",G3+INDEX(A:A, MATCH("zzzzz",D:d))-TODAY(),"")

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2011-03-14)

  4. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    How would I format to get a return on the blank "VLOOKUP" cell [K4 or I4], such as "no return" so it would indicate it is not an "empty" cell, but rather one that did not get a return [blank] from the formula? What is the return when blank, "0" or a null string? If I wanted to Conditional format the blank cell [K4, I4] how would I do it?

    How would I write the Average formula if the # days where not calculated [column C], that is , if column C did not exist?

  5. #4
    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
    How would I format to get a return on the blank "VLOOKUP" cell [K4 or I4], such as "no return" so it would indicate it is not an "empty" cell, but rather one that did not get a return [blank] from the formula??
    By changing the null to the desired text string:
    =IF(VLOOKUP("zzzzz",D:d,1)="fine",G4+INDEX(A:A,MAT CH("zzzzz",D:d))-TODAY(),"no return")
    and
    =IF(VLOOKUP("zzzzz",D:d,1)="regular",G3+INDEX(A:A, MATCH("zzzzz",D:d))-TODAY(),"no return")

    What is the return when blank, "0" or a null string?
    I do not understand this question. Could you elaborate?

    If I wanted to Conditional format the blank cell [K4, I4] how would I do it?
    The cells will not be blank if you have a formula in them...
    To conditionally format them:
    Select the cell
    Format - conditional formatting...
    Then you ahve to decide what you want the conditions to be. Is it cell based or will it require a formula. Then set the desired format. You need to be more specific if you want a detailed walk thru.

    How would I write the Average formula if the # days where not calculated [column C], that is , if column C did not exist?
    I would use the array formulas (confirm with ctrl=-shift-enter):
    =AVERAGE(IF(($D$3:$D$100="regular")*ISNUMBER($B$3: $B$100),$B$3:$B$100-$A$3:$A$100))
    and
    =AVERAGE(IF(($D$3:$D$100="fine")*ISNUMBER($B$3:$B$ 100),$B$3:$B$100-$A$3:$A$100))

    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2011-03-16)

  7. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    I have replied several times but they are not showing.
    Your formulas worked fine. Thank you.
    How would I get a string return " no start date" in column C only if I had a start date, column A, but no end date, column B. In my example, C11 would return "no start date", but C12 [and all further down] would return a null string.
    My CF question and related null string vs "0" has been figured out. Excel adds a set of quotes to the value for "cell value is equal to" and that messed up my CF. My null string, "" ended up, """".

  8. #6
    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
    =IF(ISBLANK(A3),"",IF(ISNUMBER(B3),B3-A3,"no start date"))

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2011-03-17)

  10. #7
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    That did the trick, again, thank you. You make it look so simple.

  11. #8
    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
    You make it look so simple
    It almost always looks simple when you know the answer

    Steve

  12. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    That is why I said "You make it look so simple". I know that knowledge and skills are not simple, but those who excel [no pun intended] can make it look simple or easy.
    All of us who use this forum for help appreciate the time and patience you, and all the others who contribute, give. Thank you from all of us.

  13. #10
    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
    You are very welcome. I am glad I could help.

    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
  •