Interpolate and count days

[koboos]

I have two columns. In A:A - days (2013-08-10), and in B:B - number of points gathered each of that days. See attachment.
I need to make a formula in C:C which will tell me how many days (in decimal) did it take to get every 60 000 points.
The dates in column A are midight times.

howmanydays.xlsx

[XOR LX]

Hi,

I don't understand the logic behind some of your answers. For example, between 19/01/2013 and 20/01/2013 the cumulative total went from 152,093 to 183,675, i.e. a difference of 31,582, yet you have a "60,000" next to the value of 183,675 - can you explain? Similarly for 301,009.

Also, it's not clear how you are arriving at anwers like "1.5 days". How did you determine "half a day" in these cases? What values would result in e.g. "1.75 days"? "1.0234417 days"?

Regards

[ajryan88]

Hi,

Try this formula in row 2 somewhere (column F, for example):

Please Login or Register  to view this content.

Hope this helps

[koboos]

I don't understand the logic behind some of your answers. For example, between 19/01/2013 and 20/01/2013 the cumulative total went from 152,093 to 183,675, i.e. a difference of 31,582, yet you have a "60,000" next to the value of 183,675 - can you explain? Similarly for 301,009.

Because on 2013-01-20 it went to 180 000 so the multiple of 60 000. And the day earlier it went through 120 000.

Also, it's not clear how you are arriving at anwers like "1.5 days". How did you determine "half a day" in these cases? What values would result in e.g. "1.75 days"? "1.0234417 days"?

Halves of the days are just examples withour proper counting.
Let's say from 120 000 to 152 093 it took about "half" of 19th day, from 152 093 to 180 000 it took almost all 20th day. That's why I put 1,5 days. I know it is not exact.

[koboos]

Hi,

Try this formula in row 2 somewhere (column F, for example):

Please Login or Register  to view this content.

Hope this helps

It almost works perfect but... I get cumulative answers. So it tells me the first 60 000 is 1,7 days and next 60 000 is 3,3 (it should be 3,3 - 1,7).
Another question is: can we eliminate C column and rely only on B column (not cumulative)? I put C column just for explaining.

[ajryan88]

That is strange, because I was not getting cumulative days...take a look at the attachment. We can definitely get rid of column C, I have adjusted the formula accordingly in the attachment too.

Please Login or Register  to view this content.

Hope this helps

[koboos]

Yup. It does the job perfectly. Many thanks. Now I need to investigate through the formula and see what it does step by step

BTW. The first formula didn't work correctly because I didn't notice there is range $G$1:G1. I should have put formula in column G.

Thanks again. Reputation goes to you.

[ajryan88]

Ah sorry I forgot that part when I suggested column F as an option.

Thanks, and please don't forget to mark the thread as solved too

Happy to help, have a great day!

[koboos]

Can you explain me what does double dash (--) do?

Nevermind. I googled it.