0

The goal I have in mind is to plot the different types of losses on a linear time axis to show that the losses occur in groups around the same moment.

For this goal I have a set of data on sheet 1 that looks like this:

Column A           Column B         Column C
Time               Type of loss     Loss
1/1/2016 12:00:00  4,5 or 6         between -2000 and + 2000

The date times are all just minutes with no extra seconds.

In another sheet (sheet 2à I want to format the data like this

Column A           Column B               Column C
Time               Loss type 1            Loss type 2
1/1/2016 0:00:00   Losses at that moment  Losses at that moment
1/1/2016 0:01:00   "                       "
etc..

So I used index-match to lookup the values of time and loss type. This didn't work because there were slight differences between the times. I then proceeded to use this formula on sheet 2 that includes the round function to lookup the losses.

=INDEX(Sheet1!$C$2:$C$1000,MATCH(ROUND($A2,3)&B$1,ROUND(Sheet1!$A$2:$A$1000;3)&Sheet1!$B$2:$B$1000))

The problem is that for some datetimes this rounding works and for others it doesn't. The question I have is: Is their a set number of digits I should round at or is their another solution for my problem?

Improve this question
8
  • So what would the question be? Does your rounding work or doesn't it?
    – Seth
    Nov 18, 2016 at 7:16
  • @Seth I clarified the question, is it okay now?
    – Michthan
    Nov 18, 2016 at 7:20
  • Yes. I just think you can't straight up use ROUND on a date in the fashion you want it to work. Look at this article. It has some information on how to achieve rounding on a hour/minute basis. It's more complicated than just using ROUND.
    – Seth
    Nov 18, 2016 at 7:37
  • I worked on something similar a while back. Yes there is a set limit to the amount of precision you can get and I think it's 15 digits. I'm going of pure memory, but it has to do with floating-point arithmetic. This is an important concept especially if you are doing any type of calculations very large or very small. And you should also know that rounding is only good for a result that is "good enough" but not really good for intermediate calculations.
    – ejbytes
    Nov 18, 2016 at 9:32
  • 1
    Have you tried "divide and conquer"? You say that when you round* that it works and it doesn't... It is in fact working. Have you thought about that? Divide and conquer. Break the larger problem smaller steps and then verify the data in each smaller step. You will find the solution then.
    – ejbytes
    Nov 18, 2016 at 12:49

1 Answer 1

Reset to default
1

Assuming table1 is at Sheet1 and table2 is in Sheet2.. Also B1 is 1, and C1 is 2, D1 is 3, E1 is 4, F1 is 5 & G1 is 6.

in Sheet2 cell B2 enter :

=IFERROR(INDEX(Sheet1!$C:$C,MATCH(1,((Sheet2!B$1=Sheet1!$B:$B)*(Sheet2!A2>=Sheet1!$A$2:$A$1000)*(Sheet2!A3<=Sheet1!$A$3:$A$1001),0)),"")

then press Ctrl+Shift+Enter

then drag the formula down-wards..

please share if it works.. (or not).. Hope it helps.

Improve this answer
1
  • Hi, I have switched jobs since then, so I can't really check if it works. But it seems very logical to me. A for effort!
    – Michthan
    Oct 16, 2018 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.