Finding the average using conditions in a logic function - problem

[Thomas]

Hi

I'm working on an excel sheet where I have 9 columns with dates (not all
populated) and i need to find the average of those dates depending a on
weather the "IF" function returns back a true or false answer.

eg - using only 6 dates **-***-** = not populated

Dates
1 2 3 4
5 6
01-aug-08 10-aug-08 09-aug-08 15-aug-08 **-***-** 20-aug-08
05-aug-08 20-aug-08 19-aug-08 **-***-** **-***-** 25-aug-08
10-sep-08 09-sep-08 20-sep-08 21-sep-08 23-sep-08 27-sep-08

ok, column 1 and 2 should always be populated but because different people
will use this file it is possible that they may forget to add dates in these
two columns. The question is I need an IF function to check the following
criteria:-

1. is column 1 populated - if false then neglect it
2. is column 2 populated - if false then neglect it
3. is column 2less than or equal to column 1 - if false then neglect it

from this i should get an array of just the rows just populated these
conditions. Then i need to find the sum of the difference of these dates left
after the conditions have been applied i.e from above will be
9+15+0=24 days (were row 3 give 0 as column 2 < column 1). When I try to do
it the sum function wont turn into an array and thus does not give the right
value when one of the columns is not populated so not sure how the
formulation should go.

Is this possible to do and if so is there a way to do this all in one step
using a formula?

Regards

Thomas

 

[Bernie Deitrick]

Thomas,

=SUMPRODUCT((A2:A100<>"")*(B2:B100<>"")*(B2:B100>A2:A100)*(B2:B100-A2:A100))

This will workb with dates or blanks in rows 2 to 100 of columns A and B.

HTH,
Bernie
MS Excel MVP