Match a value against a date and enter the matched value

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    I am struggling with trying to get a formula that will help to do the following we have the date broken down into individual cells , D D = AN1 and AO1 from AN1 to AU1 In the table , an identifier is indicated from F9 to P9 /Q9 toAA9 and finally from AB9 to Al9 We need to look at the first two digits of the DD ie AN1 and AO1, if the day is from the 1st of the month to the 9th of the month, we ignore the D D I am struggling with trying to get a formula that will help to do the following we have the date broken down into individual cells , D D = AN1 and AO1 from AN1 to AU1 In the table , an identifier is indicated from F9 to P9 /Q9 toAA9 and finally from AB9 to Al9 We need to look at the first two digits of the DD ie AN1 and AO1, if the day is from the 1st of the month to the 9th of the month, we ignore the D D From the 10th day of the month to the end of the month, we will now sum the two digitd ie D+D and if its greater than 9 , we mod it to a single digits We will get an answer, this answer we need to match , wth the identifier and then place in cell below the highlighted yellow cell for example if N9 = 4 and the result from the calculation is 4, we paste the 4 into N10 Likewise, we do the same for Y9 and Y10 and AJ9 and AJ 10From the 10th day of the month to the end of the month, we will now sum the two digitd ie D+D and if its greater than 9 , we mod it to a single digits We will get an answer, this answer we need to match , wth the identifier and then place in cell below the highlighted yellow cell for example if N9 = 4 and the result from the calculation is 4, we paste the 4 into N10 Likewise, we do the same for Y9 and Y10 and AJ9 and AJ 10

  • Hello and Welcome to the Forum :)


    Before getting into your underlying logic for the function ...


    Could you clarify : say day is 28 th ... so 2+8 = 10 ... and 10 / 10 is reduced to 1 ...


    Is that right ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Looking at your numerology sample file ... are you after following formula


    Code
    1. =MOD((AN1+AO1)-1,9)+1


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad to hear the formula is working as expected ...:)


    Now let's move to your logic ...


    The Number 4 appears 11 times in range F9:P9 .... so ... why should the result appear in Column N ... and not else where ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for giving me the Name of the Magic Square ...:)


    My question is : WHY ... the number must appear in N10 ... and NOT in another Column ... from F to P ...???


    see attached your test file

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sorry but your explanation is not clear at all ...

    Quote


    in our example the DD is 4th so in theory it will not fill in N10 but if its the 13th it will fill in N10 because 1+3=4


    the DD is 4th so in theory it will not fill in N10 .... SO... in practice ... WHERE AND WHY ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • the psychic number is derived from the D D of the date of birth if the D D is between the 1st and 9th of the month, we don't do anything

    but if the date is between the 10th to 31st we sum the two D +D and get a total this total is reduced to a single digit and this get s pasted onto the grid in the specific spot


    The reason this is done with the psychic because in calculations for reading of the chart, the psychic number filling the specific spot will have a value which influence's the final reading

  • Hi


    Thanx a span


    I need the number to be pasted on the grid that I attached

    I need to understand ... when there are ... in your sheet ... 11 times the same number 4 ...


    WHY .... it is the 9 th one which turns yellow .... and not another one among the 11 ... ????????

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi


    I am attaching the actual spreadsheet


    The cells are filled in based on the first two cells which is D D the next two which is M M and the next four which is the Y Y Y Y


    the ninth one is the P(psychic) the next the LP(LIfe Path and finally the K(ua)


    when reading a prediction, we add values to each of the cells and derive at a reading figure which we analyze


    the D D filling the first cells will not influence the 9th Psychic on if the date is from the 10th onwards so then a value is assigned


    this is when we look ad repeating numbers and missing numbers



    Rajan

  • Well in cell N10 you are using following formula :

    Code
    1. =IF(COUNTIFS($BF$1:$BF$1,$N9)>0,$N9,"")


    In its construction ... it is not logical ...


    Can you write in plain English what you want to accomplish with this formula ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the explanation ...


    In cell N10, you could test following formula


    Code
    1. =IF(DAY(AW1)<10,"",BF1)


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)