SOLVED

Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2360558%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360558%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20work%20on%20a%20pc.%20Excel%20version%2014.0.7268.5000%3C%2FP%3E%3CP%3EI%20need%20to%20develop%20a%20formula%20to%20help%20with%20a%20prediction%20model%20I'm%20working%20on.%3C%2FP%3E%3CP%3EIn%20simple%20terms%20I%20need%20the%20formula%20to%20predict%20an%20outcome%20of%20either%20'Passes'%20or%20'Fails'%20however%20with%20various%20combinations.%20Spreadsheet%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECombinations%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20either%20person%201%20(D1)%20OR%20person%202%20-%20where%20applicable%20(D2)%20is%20aged%200-30%20or%20over%20and%20monthly%20payment%20is%20more%20than%20%C2%A3100%20%3D%20passes%20(s0%20if%20payment%20%C2%A399%2C99%20or%20less%20it%20fails)%2C%26nbsp%3B%20if%20aged%2031%20and%20monthly%20payment%20is%20over%20%C2%A3101%20passes%2C%20if%20aged%2032%20and%20monthly%20payment%20is%20%C2%A3102%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2360558%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360593%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055724%22%20target%3D%22_blank%22%3E%40mbrant1982%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20F2%3A%3C%2FP%3E%0A%3CP%3E%3DIF(C2-MAX(D2%3AE2)%26gt%3B%3D70%2C%22Pass%22%2C%22Fail%22)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360645%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360645%22%20slang%3D%22en-US%22%3EThank%20you.%20Could%20I%20develop%20this%20even%20further%3F%3CBR%20%2F%3EIf%20I%20put%20in%20further%20columns%20calculating%20how%20many%20years%20until%20some%20reaches%2068%20(Columns%20F%26amp%3BG).%3CBR%20%2F%3EIs%20there%20then%20a%20formula%20that%20will%20read%20column%20F%26amp%3BG%20to%20identify%20the%20highest%20number%20in%20F%26amp%3BG%20and%20then%20based%20on%20that%20give%20the%20following%20if%20F%26amp%3BG%20is%3A%3CBR%20%2F%3E3%20or%20more%20and%20cell%20C%20is%20%C2%A3378%20or%20more%20%3D%20passes%3CBR%20%2F%3E4%20or%20mote%20%26amp%3B%20cell%20C%20is%20%C2%A3293%20or%20more%20%3D%20passes%3CBR%20%2F%3E5%20or%20more%20%26amp%3B%20cell%20C%20is%20%C2%A3245%20or%20more%20%3D%20passes%3CBR%20%2F%3E6%20or%20more%20%26amp%3B%20cell%20C%20is%20%C2%A3208%20or%20more%3D%20passes%3CBR%20%2F%3EAnd%20then%207-30%20with%20the%20differing%20amount%20for%20%C2%A3%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360665%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055724%22%20target%3D%22_blank%22%3E%40mbrant1982%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20%22rule%22%20behind%20the%20sequence%20378%2C%20293%2C%20245%2C%20208%2C%20...%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360667%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360667%22%20slang%3D%22en-US%22%3EThere%20isn't%20a%20fixed%20rule%2Fpattern%20of%20such.%20The%20numbers%20are%20independent.%3CBR%20%2F%3E%3CBR%20%2F%3EThese%20numbers%20are%20fixed%20numbers%20based%20on%20another%20piece%20of%20work%2C%20so%20standalone%20numbers.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20that%20makes%20sense.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360708%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055724%22%20target%3D%22_blank%22%3E%40mbrant1982%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20version%20of%20your%20workbook%20with%20the%20new%20columns%2C%20and%20with%20a%20list%20of%20the%20number%20of%20years%20with%20the%20corresponding%20values%20to%20be%20used%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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