I'm having a bad day and can't think of how to simplify the below formula
=IF(OR(Affordability!D25='Afford Validation'!M4,Affordability!D25='Afford Validation'!M5,Affordability!D25='Afford Validation'!M6),MAX('Afford Validation'!E16:E19),OR(Affordability!D25='Afford Validation'!M7,Affordability!D25='Afford Validation'!M8,Affordability!D25='Afford Validation'!M9,Affordability!D25='Afford Validation'!M10)*MAX('Afford Validation'!E17:E19))
Basically, it's a looking at a field (D25). If the field's value is A,B or C then it needs to use the Max of 4 values (E16:E19), if the value of D25 is D,E,F or G then it needs to use the Max of 3 values (E17:E19).
I know I've overcomplicated it but anything I try to make it more simple seems to result in an error. I was originally trying,
=IFS(Affordability!D25='Afford Validation'!M4:M6,MAX('Afford Validation'!E16:E19),Affordability!D25='Afford Validation'!M7:M10,MAX('Afford Validation'!E17:E19))
Any help is appreciated
=IF(OR(Affordability!D25='Afford Validation'!M4,Affordability!D25='Afford Validation'!M5,Affordability!D25='Afford Validation'!M6),MAX('Afford Validation'!E16:E19),OR(Affordability!D25='Afford Validation'!M7,Affordability!D25='Afford Validation'!M8,Affordability!D25='Afford Validation'!M9,Affordability!D25='Afford Validation'!M10)*MAX('Afford Validation'!E17:E19))
Basically, it's a looking at a field (D25). If the field's value is A,B or C then it needs to use the Max of 4 values (E16:E19), if the value of D25 is D,E,F or G then it needs to use the Max of 3 values (E17:E19).
I know I've overcomplicated it but anything I try to make it more simple seems to result in an error. I was originally trying,
=IFS(Affordability!D25='Afford Validation'!M4:M6,MAX('Afford Validation'!E16:E19),Affordability!D25='Afford Validation'!M7:M10,MAX('Afford Validation'!E17:E19))
Any help is appreciated