I wish to average info in columns that is a challenge due to missing data. The data involves mileage over a three year period for motor vehicles. Some of the data will be missing b/c of rec'd date as well. I need the formula to calculate the difference of mileage from each year to the next, sum the total, then ave for the entire period. If one is missing, say the middle year mileage, I need to average the two known periods then ave for three.
One row will read current mileage(c2)=54000, '06 miles(d3)=0, '05 miles(e2)=52100, and '04 miles(f2)=51000. The answer should equal an average of 3000. In other rows the vehicle is a 2005 model is has just two years of data to report.
How can have the formula read the information and provide an accurate average based on the above?
Bookmarks