May 17 2021 04:27 AM - edited May 17 2021 06:16 AM
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
May 17 2021 04:35 AM
May 17 2021 05:03 AM - edited May 17 2021 05:03 AM
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 £?
May 17 2021 05:08 AM
What is the "rule" behind the sequence 378, 293, 245, 208, ...?
May 17 2021 05:16 AM
May 17 2021 05:29 AM - edited May 17 2021 06:04 AM
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.
May 17 2021 06:18 AM
May 17 2021 06:27 AM
SolutionIn 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)
May 17 2021 07:10 AM
May 17 2021 07:15 AM
See the series of tutorials that begins at Formulas and Functions
May 17 2021 06:27 AM
SolutionIn 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)