Forum Discussion
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
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
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 £?What is the "rule" behind the sequence 378, 293, 245, 208, ...?
