SOLVED

Formula help

Copper Contributor

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

9 Replies

@mbrant1982 

In F2:

=IF(C2-MAX(D2:E2)>=70,"Pass","Fail")

Fill down.

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

@mbrant1982 

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

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

@mbrant1982 

Could you attach a version of your workbook with the new columns, and with a list of the number of years with the corresponding values to be used? Thanks in advance.

I have uploaded 'SAMPLE 2' to my original post. I couldn't seem to attach to this message. Column M shows how long until a person is 68 and what the payment must be. I hope this makes sense. Not the easiest to explain.
Thanks for your help.
MB
best response confirmed by mbrant1982 (Copper Contributor)
Solution

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

Thank you. That's amazing, I would never have got them myself.
Do you have any recommendations for courses I could to develop my skillset on formulas?

@mbrant1982 

See the series of tutorials that begins at Formulas and Functions 

1 best response

Accepted Solutions
best response confirmed by mbrant1982 (Copper Contributor)
Solution

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

View solution in original post