Calc miles in one column based on info in another

[teajuana]

Newbie here, thanks in advance for your help.

Here is my sample WB


http://hotfile.com/dl/136404528/302dca1/FMDetailedGPSReport_11_30_2011_(Autosaved).xlsm.html


This had been filtered by the "Status" column when uploaded, please remove that filter to see what I am talking about.


I am trying to calc the distance driven between "Roof Lights On" and "Roof LIghts Off", this would be 1 "trip", then get a total distance for all trips. These reports will always be of different length though the basic format should remain the same. This solution should be dynamic because the "RLOn" and "RLOff" will always have a different # of rows between them. I have tried if statements but can't get the calc dynamically to work with the varying # of rows. Also tried lookup but not sure how to use it.


Oh yah, can this be automated at all?


Thanks again.

 

[NARAYANK991]

Hi ,


Interesting problem. Try the following :


1. In column N , in cell N9 , enter the following formula :


=IF(L9<>0,IF(E9="Roof Lights ON",L9,IF(E9="Roof Lights OFF",L9-N8,N8)),N8)


Copy this formula for the remaining part of column N , as long as there is data in the other columns ; in your sample worksheet , copy till cell N79.


2. In column O , in cell O9 , enter the following formula :


=SUMIF(E9:E79,"Roof Lights OFF",N9:N79)


Of course , the choice of columns N and O are arbitrary ; you can choose any unused columns.


Narayan

 

[teajuana]

Thanks, for the help, but.....


I am sorry, I should have clearified, L9:l79 and M9:m79 are not part of the original report, they are part of my failed attempt to solve this problem. I will try you solution in L and M as I hope to find the most simple and efficient way to do this.


I am not clear as to what the second formula is supposed to do. It returns an ever decreasing value but I can't seem to figure out what it is telling me.


When I go full scale with this, I will probably have 10-12 similar reports but each being 4 or 5 times the length of this.

 

[NARAYANK991]

Hi ,


Whatever I posted depends on your worksheet having all their existing formulae in their present cells ; please do not substitute the formulae I have posted in place of any of yours.


The formulae in column N are just repeating the mileage when the "Roof Lights ON" signal was entered ; this is so that when the "Roof Lights OFF" signal is entered , the mileage between the two is just the current cell - the immediately previous cell.


The formula in cell O9 is adding all the entries in column N wherever the "Roof Lights OFF" signal has been entered ; since the relevant cell in column N has the difference , we get what we want. This formula is to be entered only in cell O9. In case you want that there should be a separate value for each difference between "Roof Lights OFF" and "Roof Lights ON" , we will have to change this formula. I'll get back on this.


Narayan

 

[NARAYANK991]

Hi ,


In column O , in cell O9 , enter the following formula :


=IF(E9="Roof Lights OFF",N9,"")


Copy this to all the remaining cells in column O , as far as your data is present.


Narayan

 

[teajuana]

You are a rock star!!!!! Thank you soooo much.


Would it be possible to inbed this last formula into the first?

 

[NARAYANK991]

Hi ,


Thanks.


I don't think so , since the formulae in column O are dependent on those in column N.


If a different formula is used in column N , probably you can achieve whatever you want with just the one column. I'll keep trying and post the solution here if I get it.


Narayan