excel dates quicker way to work with missing dates

jimjohn

Board Regular
Joined
Nov 14, 2007
Messages
107
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!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Code:
=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
Code:
=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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,658
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top