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

 

[etaf]

a real blank cell is considered zero, but a cell with a formula which return "" (empty string) is not considered zero.

interesting, thanks for the clarification

 

[felixstraube]

Here you can see the comparison results:

Libro1.xlsx
	A	B	C	D
1			>300	<300
2	Real empty cell		FALSE	TRUE
3	Empty string (not empty cell)		TRUE	FALSE
4	Any string	abc	TRUE	FALSE
5	value	0	FALSE	TRUE
6	value	400	TRUE	FALSE
Hoja1

Cell Formulas
Range	Formula
C2:C6	C2	=B2>300
D2,D4:D6	D2	=B2<300
D3	D3	=B3<2^31
B3	B3	=""
B4	B4	="abc"

 

[SaraWitch]

Amazing; I am in awe! Thank you, @felixstraube! And thank you, @etaf too.

Ideally, I would only like the latest figure calculated in the formula for the subsidy figure (i.e., the eleven relevant cells EP to IB). For example, if a previous subsidy was over £300 but the latest is not, let's say it's under £130, then the RAG rating would be "G", as long as the other criteria (e.g., housing benefit in cell ED and debt in cell IN was in line). Can this be done at all?

 

[felixstraube]

@SaraWitch
You are sharing the file you are working on. Me or any other that has the link you provided in the first post can edit/modify your file.
I would advise you to stop sharing that file ASAP

 

[SaraWitch]

Thank you (thought I already had stopped sharing - but have actually removed it from that drive now).

Is there a way of editing the formula to just calculate latest subsidy figure at all?

 

[felixstraube]

I don't have your original file anymore. Which would be the latest subisdy?

 

[SaraWitch]

It could be any in cells EP, EY, FH, FQ, FZ, GI, GR, HA, HJ, HS or IB. So, the first (EP) will always have an amount but if there is another housing benefit claim, the subsidy could change, so I would like the formula to use the next subsidy amount in EY, and so forth.

Let me know if you would like to re-share the workbook...

 

[felixstraube]

Ok so the cells EP, EY, FH, FQ, FZ, GI, GR, HA, HJ, HS, IB will be filled/populated from left to right? And you want to perform the value comparison on right most that has a value?

 

[felixstraube]

How about this in B8 and copy down?:

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

 

[SaraWitch]

Honestly, @felixstraube, I can't express my gratitude enough! This was way beyond my capabilities and has totally blown my mind! I have also learnt something today, for which I'm also grateful. Thank you sooooo much!! Amazing!