Results 1 to 10 of 10

20110313, 19:55 #1
 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.

20110314, 08:20 #2
 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 ctrlshiftenter):
=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 ctrlshiftenter):
=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

The Following User Says Thank You to sdckapr For This Useful Post:
skipro (20110314)

20110315, 10:10 #3
 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?

20110315, 10:59 #4
 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??
=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?
If I wanted to Conditional format the blank cell [K4, I4] how would I do it?
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?
=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

The Following User Says Thank You to sdckapr For This Useful Post:
skipro (20110316)

20110316, 21:31 #5
 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, """".

20110317, 07:57 #6
 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),B3A3,"no start date"))
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
skipro (20110317)

20110317, 10:36 #7
 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.

20110317, 12:05 #8
 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
Steve

20110317, 21:34 #9
 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.

20110317, 22:47 #10
 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