Forum Discussion

mbrant1982's avatar
mbrant1982
Copper Contributor
May 17, 2021
Solved

Formula help

Hi I work on a pc. Excel version 14.0.7268.5000 I need to develop a formula to help with a prediction model I'm working on. In simple terms I need the formula to predict an outcome of either 'Pass...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 17, 2021

    mbrant1982 

    In H2:

     

    =IF(C2>=VLOOKUP(MAX(F2:G2),$M$2:$N$17,2),"Pass","Fail")

     

    Fill down. (Notice that the formula doesn't use the entire range in columns M and N. Once the value in column N remains the same, the rest isn't needed)

  • mbrant1982's avatar
    mbrant1982
    Copper Contributor
    May 17, 2021

    Thank you. Could I develop this even further?
    If I put in further columns calculating how many years until some reaches 68 (Columns F&G).
    Is there then a formula that will read column F&G to identify the highest number in F&G and then based on that give the following if F&G is:
    3 or more and cell C is £378 or more = passes
    4 or mote & cell C is £293 or more = passes
    5 or more & cell C is £245 or more = passes
    6 or more & cell C is £208 or more= passes
    And then 7-30 with the differing amount for £?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 17, 2021

      mbrant1982 

      What is the "rule" behind the sequence 378, 293, 245, 208, ...?

      • mbrant1982's avatar
        mbrant1982
        Copper Contributor
        May 17, 2021
        There isn't a fixed rule/pattern of such. The numbers are independent.

        These numbers are fixed numbers based on another piece of work, so standalone numbers.

        I hope that makes sense.

        Thanks 🙂

Resources