Forum Discussion

Gina Leite's avatar
Gina Leite
Copper Contributor
Nov 08, 2017

I need help to find a formula that return value related within a given range of time.

Hi, here is my challenge:   In the table A, I need to refer a date to a certain semester.  I have created a matrix that establishes that "Semester 1" (lets say B6)  is between two dates (C6 and D6...
Formulas & Functions
Need Help
Gina Leite's avatar
Gina Leite
Copper Contributor
Nov 08, 2017

Hi Sergei, thank you for your assistant. 

It has not worked, i think I might be missing a step.

If you have the time and you don´t mind, can you access this https://wcs1-my.sharepoint.com/personal/gleite_wcs_org/_layouts/15/guestaccess.aspx?docid=17f47e5cbe1c643e780570716f5486ae1&authkey=AeKH1gHmnQiXHVCtK3pSAdY on excel online to help me out?

Thank you very much !

gina

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 08, 2017

Gina, I'll do bit later, but, yes, such formula won't work in Excel Online if you use it. Will check the file and try to find alternative.

  • Gina Leite's avatar
    Gina Leite
    Copper Contributor
    Nov 08, 2017

    Hi Sergei

    I use both Excel online and desktop. 

    I would be happy to use Excel desktop to solve that problem, so you could download the file and try on our computer :)

    many thanks!

    Gina

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 08, 2017

      Thank you Gina, that will be easier

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 08, 2017

        Hi Gina,

         

        Most complex thing is to download the file...

        Perhaps you didn't enter the formula as array one - press Ctrl and Shift simultaneously, press Enter then and now release all buttons.

        Please see the formula

        =IFERROR(INDEX($B$4:$B$7,MATCH(1,(G5>=$C$4:$C$7)*(G5<=$D$4:$D$7),0)), "out of project terms")

        in attached file

Resources