excel dates quicker way to work with missing dates

[jimjohn]

i have one column(A) that includes all dates from june 2003 till june 2008 (but only weekdays are included).

i have another column(C) that includes most of these dates (but some days are missing), and then beside this column is another one (D) with the rates corresponding to those dates in column C.

what i want to do is, for each missing date (weekdays only), i want to take an average of the rate on the day before and the rate the day after.

is there a quick way to do this? right now, ive just been matching each date on column A with column C, returning a 1 if theyre the same date and a 0 otherwise. So when I see a 1, I copy over the rates corresponding to those rows in D over to my new column. and whenever i see a 0, i calculate the average and then move the rest of Column C and D down one.

its not so much the average calulations that take a while, its this whole process of finding a 0 and then each time i do, i have to move the rest of the columns down one and then i have to change my match formula. any ideas of a quicker way to do this? thanks!

 

[Domski]

Hi,

It's not very elegant but:

- Create your list of weedays Jun 03-Jun 08 using series fill
- Use vlookup to bring the rates into that list
- Paste special values and Find...replace any 0's or #N/A
- Select the rates column and press F5...Special...Blanks
- Enter your formula e.g. =AVERAGE(A1,A3) and confirm with Ctrl+Enter

You get some circular references if there's 2 consecutive days without a rate but you can either change the calculation iterations in options or deal with them manually.

Dom

 

[Gerald Higgins]

Yes there are quicker ways.

First, use a VLOOKUP formula, to cross reference all the rates in Col D to the relevant date in Col A.
Like this

=if(isna(vlookup(a1,c$1:d$100,2,false)),"",vlookup(a1,c$1:d$100,2,false)

If the date in Col A is found in Col C, this will return the relevant rate from Col D.
If the date in Col A is NOT found in Col C, this will return a blank.

Then, you can write a formula to fill in the gaps for missing rates.
For example, let's say you've put my suggested formula in Col E.
Use something like this

=if(E2="",(e1+e3)/2,+e2)

This will work as long as you only have single days missing. If you have strings of several days missing together, you'll need something more complicated.

 

[barry houdini]

This formula in B1 copied down will find the value from column D that corresponds to the date in column A, if that date doesn't exist [in column C] it gives the average of the previous date and the next date, whatever the gap

=(LOOKUP(A1,C$1:D$1300)+INDEX(D$1:D$1300,MATCH(A1,C$1:C$1300)+(LOOKUP(A1,C$1:C$1300)<>A1)))/2