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 'Passes' or 'Fails' however with various combinations. Spreadsheet attached.

 

Combinations are:

 

If either person 1 (D1) OR person 2 - where applicable (D2) is aged 0-30 or over and monthly payment is more than £100 = passes (s0 if payment £99,99 or less it fails),  if aged 31 and monthly payment is over £101 passes, if aged 32 and monthly payment is £102 

 

Thank you

  • 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)

9 Replies

    • mbrant1982's avatar
      mbrant1982
      Copper Contributor

      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 £?

Resources