Averaging many non-contiguous cells if they are blank
 

I have columns set up to record payments (one for the date and one for the
amount). Currently I have columsn enough for 13 payments. So, imagine that
the invoice number is in Col A. Col B has the date the invoice was received.
Col C is for the date of the first payment. Column D is for the amount of
that payment. Col E is for the date of the next payment. Col F is for the
amount of that next payment. And so on.

There will be instances where there won't be anything in Col C-D but there
will be in Col E-F and so on. Some invoices can use all 13 sets of columns
and some might use one and others might use any combination of these 13 sets.

I need to find the average of the dates as compared to when the invoice was
received - basically, how long did it take to pay the invoice. I can't just
average the first column of each 13 sets because if there's no date in the
column, it shouldn't be included.

Does anyone know how to write a formula that will average the number of days
between these dates? I am using Excel 2003. Thanks.

Averaging many non-contiguous cells if they are blank
 

Hi Nadine,

The AVERAGE function disregards empty cells, so it shouldn't matter whether some columns are empty.

There does seem to be some inconsistency in your problem description, though: If Column C is for the first payment, how can you have
payments, yet there will be "instances where there won't be anything in Col C-D"?

--
Cheers
macropod
[Microsoft MVP - Word]


"Nadine" wrote in message ...
I have columns set up to record payments (one for the date and one for the
amount). Currently I have columsn enough for 13 payments. So, imagine that
the invoice number is in Col A. Col B has the date the invoice was received.
Col C is for the date of the first payment. Column D is for the amount of
that payment. Col E is for the date of the next payment. Col F is for the
amount of that next payment. And so on.

There will be instances where there won't be anything in Col C-D but there
will be in Col E-F and so on. Some invoices can use all 13 sets of columns
and some might use one and others might use any combination of these 13 sets.

I need to find the average of the dates as compared to when the invoice was
received - basically, how long did it take to pay the invoice. I can't just
average the first column of each 13 sets because if there's no date in the
column, it shouldn't be included.

Does anyone know how to write a formula that will average the number of days
between these dates? I am using Excel 2003. Thanks.