Formula not working as expected

[SaraWitch]

Hello peeps, and a happy new year to everyone!

I have a formula that returns "R", "A", or "G" depending on values in other cells, and that cell is conditionally formatted to change to appropriate colour (red, amber or green). My issue is that the formula is returning "R" no matter the values elsewhere. I have tried rearranging the formula (cells B10 to B13) but this isn't working either. There are 11 cells for the 'subsidy loss' and the formula needs to return appropriate letter for the latest entry in these cells (EP, EY, FH, FQ, FZ, GI, GR, HA JH, HS, AND IB) as well as the other criteria (cells D, ED (housing benefit), and IN (debt)) as per the note in Cell B5.

I have also replicated the formula in Sheet 1, which is working - to a point.

Here is a link to the workbook because it's too large and complicated to copy just the cells in question: EA Placements Manager

Any help would be greatly appreciated (especially as I've been at this since before Christmas! )

 

[felixstraube]

You are most welcome. Thank you for the feedback!

 

[SaraWitch]

Hi @felixstraube!

I've had to change one of my column's (ED) data validation list to, "No, Yes x 1, Yes x 2, Yes x3"... etc. to "Yes x 10" and now need "R" to return if any Yes is selected.

I have tweaked the eighth line of the formula to ..."Yes*" but it's not recognising the wildcard:

=LET(
s,N(HSTACK(EP8, EY8, FH8, FQ8, FZ8, GI8, GR8, HA8, HJ8, HS8, IB8)),
sf,(s<>0)*SEQUENCE(,11),
rightMostValue,IF(MAX(sf)=0,0,FILTER(s,sf=MAX(sf))),
d, HSTACK(rightMostValue, IN8),
upperLimit, {300,2000},
lowerLimit, {130,800},
res,IF(D8 = "", "", IF(OR(ED8 = "", ED8 = "Yes*", SUM((d > upperLimit) * 1)), "R", IF(SUM((d <= upperLimit) * (d > lowerLimit)), "A", "G"))),
res
)

Any help would be appreciated

 

[SaraWitch]

Ah, I think I've figured it out - I was going about it from a 'Yes' point of view!

I've tweaked line eight to say <> "No", which seems to work:

=LET(
s,N(HSTACK(EP8, EY8, FH8, FQ8, FZ8, GI8, GR8, HA8, HJ8, HS8, IB8)),
sf,(s<>0)*SEQUENCE(,11),
rightMostValue,IF(MAX(sf)=0,0,FILTER(s,sf=MAX(sf))),
d, HSTACK(rightMostValue, IN8),
upperLimit, {300,2000},
lowerLimit, {130,800},
res,IF(D8 = "", "", IF(OR(ED8 = "", ED8 <> "No", SUM((d > upperLimit) * 1)), "R", IF(SUM((d <= upperLimit) * (d > lowerLimit)), "A", "G"))),
res
)

Thank you anyway