Average with variable, and days to average

Topic

New Reply

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.

Reply | Quote

Replies

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,MATCH(“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

Reply | Quote

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?

Reply | Quote

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,MATCH(“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

Reply | Quote

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, “”””.

Reply | Quote

=IF(ISBLANK(A3),””,IF(ISNUMBER(B3),B3-A3,”no start date”))

Steve

Reply | Quote

Steve,
That did the trick, again, thank you. You make it look so simple.

Reply | Quote

You make it look so simple

It almost always looks simple when you know the answer:D

Steve

Reply | Quote

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.

Reply | Quote

You are very welcome. I am glad I could help.

Steve

Reply | Quote