Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I ignore blank cells while averaging the solutions of equat | Excel Discussion (Misc queries) | |||
Averaging Blank Cells | Excel Worksheet Functions | |||
Averaging blank or cells with Zeros in them. | Excel Worksheet Functions | |||
Averaging non contiguous numbers | Excel Discussion (Misc queries) | |||
Averaging blank cells | Excel Discussion (Misc queries) |