Help on simplifying a formula

[cmerrick]

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

 

[steve the fish]

You could use a countif for example:

=IF(COUNTIF('Afford Validation'!M4:M6,Affordability!D25),MAX('Afford Validation'!E16:E19),IF(COUNTIF('Afford Validation'!M7:M10,Affordability!D25),MAX('Afford Validation'!E17:E19),))

 

[Peter_SSs]

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

Assuming the D25 does exist in the M4:M10 range you could try

=MAX(INDEX('Afford Validation'!E16:E17,1+(MATCH(Affordability!D25,'Afford Validation'!M4:M10,0)>3)):'Afford Validation'!E19)

BTW, what is the name of the sheet that this formula is in?

Also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cmerrick]

Thank you both for your help. I've gone with the first response from Steve the Fish.

Peter - thanks for the tip re Excel Version and Platform!

 

[Peter_SSs]

You're welcome.

I've gone with the first response from Steve the Fish.

That's fine, but please take a look at this: Mark as Solution

Thanks for updating your profile.

BTW, what was the answer to my question?

what is the name of the sheet that this formula is in?

 

[cmerrick]

You're welcome.


That's fine, but please take a look at this: Mark as Solution

Thanks for updating your profile.

BTW, what was the answer to my question?

Sorry, the name of the sheet is/was 'Afford Validation'

 

[Peter_SSs]

the name of the sheet is/was 'Afford Validation'

Thanks.
In that case you should remove all references to that sheet name from the formula. Apart from making the formula considerably shorter and easier to read, using the sheet name of the sheet that a formula is on can actually cause problems in some circumstances.

So, for example, my formula would become:

=MAX(INDEX(E16:E17,1+(MATCH(Affordability!D25,M4:M10,0)>3)):E19)