• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HLOOKUP with multiple criteria

Hi ,

In a previous post , you mentioned :
On your previous attachment, example score of 07:41 currently gives -5. As the time hasn't yet reached the threshold for -5 (07:42) the points returned should be -2.
Click to expand...
Let us try to understand this :

Since the example time of 07:41 is between the two end-points 07:21 and 07:42 , the result will be between the corresponding scores for these two end-points , which are :

07:21 ..... -2

07:42 ..... -5

Since 07:41 is greater than or equal to 07:21 , we should return -2.

In your latest post , you mention :
Example time 06:59. The threshold is 07:00 for 0 points, and 06:39 for 2 points. The logic here is it should choose 0 as it is lower than 07:00 but higher than 06:39.
Click to expand...
If we follow the same logic as above , the example time of 06:59 is between the two end-points 06:39 and 07:00 , the result will be between the corresponding scores for these two end-points , which are :

06:39 ..... 2

07:00 ..... 0

Since 06:59 is greater than or equal to 06:39 , we should return 2.

This is how functions will work , but you want a different kind of logic to be applied , which I am not able to understand.

Can you clarify ?

Narayan
 
Hi Narayan,

You are right it is a very confusing scale and logic pattern.

I've mapped out some better logic examples in the attached which should help find where the issue is. I think it's a small part of the logic which is the problem where it's taking the level above / below when it should be taking the opposite.

Please take a look at the attached and see if it makes better sense. I've conditionally formatted where the logic falls down.

Thank you Narayan.
 

Attachments

  • Scoring System (6).xlsx
    13.8 KB · Views: 3
Hi ,

This is an totally unexpected turn of events ; below the 0 score the behaviour is opposite to the behaviour above the 0 score.

I'll take some days to look into how this can be done.

Narayan
 
Thank you Narayan.

Can you see why I need the logic to work the way I suggested in the expected outcomes? Does it make sense?

Many thanks
 
Hi ,

I can understand what you have explained , though why it should be this way is a different matter. Anyway , if that is your logic , I cannot dispute it.

I'll see how it can be done , though I think that it cannot be done without using helper columns.

Narayan
 
I sincerely appreciate your help Narayan. I could not achieve this without your assistance.

I am open to bigger, less efficient formula's if it will better help achieve the goal. Would long nested IF statement's to compare each threshold with lookups work?
 
You must log in or register to reply here.
Back
Top