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]

i would change ALL the same results to an OR

=IF(D11="","",IF(IB11<=130,"G",IF(HS11<=130,"G",IF(HJ11<=130,"G",IF(HA11<=130,"G",IF(GR11<=130,"G",IF(GI11<=130,"G",IF(FZ11<=130,"G",IF(FQ11<=130,"G",IF(FH11<=130,"G",IF(EY11<=130,"G",IF(EP11<=130,"G",IF(IN11<800,"G",IF(AND(IB11>130,IB11<=300),"A",IF(AND(HS11>130,HS11<=300),"A",IF(AND(HJ11>130,HJ11<=300),"A",IF(AND(HA11>130,HA11<=300),"A",IF(AND(GR11>130,GR11<=300),"A",IF(AND(GI11>130,GI11<=300),"A",IF(AND(FZ11>130,FZ11<=300),"A",IF(AND(FQ11>130,FQ11<=300),"A",IF(AND(FH11>130,FH11<=300),"A",IF(AND(EY11>130,EY11<=300),"A",IF(AND(EP11>130,EP11<=300),"A",IF(AND(IN11>=800,IN11<=2000),"A",IF(ED11="","R",IF(ED11="No","R",IF(IB11>300,"R",IF(HS11>300,"R",IF(HJ11>300,"R",IF(HA11>300,"R",IF(GR11>300,"R",IF(GI11>300,"R",IF(FZ11>300,"R",IF(FQ11>300,"R",IF(FH11>300,"R",IF(EY11>300,"R",IF(EP11>300,"R",IF(IN11>2000,"R","")))))))))))))))))))))))))))))))))))))))

so group all the G results with an OR
which you have
=IF(D11="","",IF(IB11<=130,"G",IF(HS11<=130,"G",IF(HJ11<=130,"G",IF(HA11<=130,"G",IF(GR11<=130,"G",IF(GI11<=130,"G",IF(FZ11<=130,"G",IF(FQ11<=130,"G",IF(FH11<=130,"G",IF(EY11<=130,"G",IF(EP11<=130,"G",IF(IN11<800,"G"
so
=OR(IB11<=130,HS11<=130,HJ11<=130,HA11<=130,GR11<=130,GI11<=130,FZ11<=130,FQ11<=130,FH11<=130,EY11<=130,EP11<=130,IN11<800 )
you may see the issue

so now you have 4 nested IF - 1 for blank - and 3 for G, A, R

i'll look at further

 

[etaf]

i have reduced the IF to ORs
"G" is
OR(IB11<=130,HS11<=130,HJ11<=130,HA11<=130,GR11<=130,GI11<=130,FZ11<=130,FQ11<=130,FH11<=130,EY11<=130,EP11<=130,IN11<800)
Which returned TRUE in B11

FOR "A" =
OR(AND(IB11>130,IB11<=300),AND(HS11>130,HS11<=300),AND(HJ11>130,HJ11<=300),AND(HA11>130,HA11<=300),AND(GR11>130,GR11<=300),AND(GI11>130,GI11<=300),AND(FZ11>130,FZ11<=300),AND(FQ11>130,FQ11<=300),AND(FH11>130,FH11<=300),AND(EY11>130,EY11<=300),AND(EP11>130,EP11<=300),AND(IN11>=800,IN11<=2000))
which returned FALSE

and then
"R"
OR(ED11="",ED11="No",IB11>300,HS11>300,HJ11>300,HA11>300,GR11>300,GI11>300,FZ11>300,FQ11>300,FH11>300,EY11>300,EP11>300,IN11>2000)
WHICH ALSO returned TRUE - so maybe an issue

=IF(D11="","",IF(OR(IB11<=130,HS11<=130,HJ11<=130,HA11<=130,GR11<=130,GI11<=130,FZ11<=130,FQ11<=130,FH11<=130,EY11<=130,EP11<=130,IN11<800),"G",IF(OR(AND(IB11>130,IB11<=300),AND(HS11>130,HS11<=300),AND(HJ11>130,HJ11<=300),AND(HA11>130,HA11<=300),AND(GR11>130,GR11<=300),AND(GI11>130,GI11<=300),AND(FZ11>130,FZ11<=300),AND(FQ11>130,FQ11<=300),AND(FH11>130,FH11<=300),AND(EY11>130,EY11<=300),AND(EP11>130,EP11<=300),AND(IN11>=800,IN11<=2000)),"A",IF(OR(ED11="",ED11="No",IB11>300,HS11>300,HJ11>300,HA11>300,GR11>300,GI11>300,FZ11>300,FQ11>300,FH11>300,EY11>300,EP11>300,IN11>2000),"R",""))))

maybe easier to find the issue

can you give some example of when its wrong

i changed the formatting - so i could see the text and get a "G" NOT an R

the spreadsheet - is rather large to go through
does that help ?

the spreadsheet has crashed a couple of times on my macbook pro - M1 with 16gb ram , so thats interesting

 

[SaraWitch]

Thank you, @etaf - that feels closer than I have been, and makes sense! I really appreciate you taking a look, thank you

Ignore this message - posted before I saw your other post - just checking now, thank you.

 

[etaf]

that is giving me an error too - but the formula i posted is OK

=IF(D11="","",IF(OR(IB11<=130,HS11<=130,HJ11<=130,HA11<=130,GR11<=130,GI11<=130,FZ11<=130,FQ11<=130,FH11<=130,EY11<=130,EP11<=130,IN11<800),"G",IF(OR(AND(IB11>130,IB11<=300),AND(HS11>130,HS11<=300),AND(HJ11>130,HJ11<=300),AND(HA11>130,HA11<=300),AND(GR11>130,GR11<=300),AND(GI11>130,GI11<=300),AND(FZ11>130,FZ11<=300),AND(FQ11>130,FQ11<=300),AND(FH11>130,FH11<=300),AND(EY11>130,EY11<=300),AND(EP11>130,EP11<=300),AND(IN11>=800,IN11<=2000)),"A",IF(OR(ED11="",ED11="No",IB11>300,HS11>300,HJ11>300,HA11>300,GR11>300,GI11>300,FZ11>300,FQ11>300,FH11>300,EY11>300,EP11>300,IN11>2000),"R",""))))

=IF(D11="","",IF(OR(IB11<=130,HS11<=130,HJ11<=130,HA11<=130,GR11<=130,GI11<=130,FZ11<=130,FQ11<=130,FH11<=130,EY11<=130,EP11<=130,IN11<800),"G",IF(OR(AND(IB11>130,IB11<=300),AND(HS11>130,HS11<=300),AND(HJ11>130,HJ11<=300),AND(HA11>130,HA11<=300),AND(GR11>130,GR11<=300),AND(GI11>130,GI11<=300),AND(FZ11>130,FZ11<=300),AND(FQ11>130,FQ11<=300),AND(FH11>130,FH11<=300),AND(EY11>130,EY11<=300),AND(EP11>130,EP11<=300),AND(IN11>=800,IN11<=2000)),"A",IF(OR(ED11="",ED11="No",IB11>300,HS11>300,HJ11>300,HA11>300,GR11>300,GI11>300,FZ11>300,FQ11>300,FH11>300,EY11>300,EP11>300,IN11>2000),"R",""))))

from what i can see you have )) after the AND's which are not needed and so its closing off the IF 0 and on all the AND's

highlighted a few below

so
OR(AND(IB10>130,IB10<=300)),(AND(HS10>130,HS10<=300)),(AND(HJ10>130,HJ10<=300)),(AND(HA10>130,HA10<=300)),(AND(GR10>130,GR10<=300)),(AND(GI10>130,GI10<=300)),(AND(FZ10>130,FZ10<=300)),(AND(FQ10>130,FQ10<=300)),(AND(FH10>130,FH10<=300)),(AND(EY10>130,EY10<=300)),(AND(EP10>130,EP10<=300)),(AND(IN10>=800,IN10<=2000)),
is wrong

 

[felixstraube]

I think the problem you have is that you compare a cell with content "" (empty string) to a numeric value.
That is, you compare "">300 (like FH8 for example). I think you expect that to return FALSE, but it returns TRUE.
A string (even empty ones) will always be greater than numbers.

So you will have to adjust you formula for that or change the formula in FH8 and so forth so that they return a numeric value instead of "".

 

[SaraWitch]

Sorry, @etaf, posted previous before I saw your update, but it still doesn't appear to work (I've updated sheet 1 as well, if easier): EA Placements Manager...

 

[etaf]

ok,
but you only have in row 11 - needs to be in all rows copied

BUT i get G in all rows now

can you setup with some expected results for A, G , R ????

as

felixstraube​

post
BUT have you considered that a Blank cell is considered a zero , so any of the conditions for <130 if those are blank - than thats a zero and TRUE it is less than 130

 

[etaf]

you also dont need the AND , as you have already tested the same cells to be less than 300 , and in the case of IN11 less than 800

OR(IB11<=300,HS11<=30,HJ11<=300,HA11<=300,GR11<=300,GI11<=300,FZ11<=300,FQ11<=300,FH11<=300,EY11<=300,EP11<=300,IN11<=2000)

SO in ROW 11
copy up to 8 and down

=IF(D11="","",IF(OR(IB11<=130,HS11<=130,HJ11<=130,HA11<=130,GR11<=130,GI11<=130,FZ11<=130,FQ11<=130,FH11<=130,EY11<=130,EP11<=130,IN11<800),"G",IF(OR(IB11<=300,HS11<=30,HJ11<=300,HA11<=300,GR11<=300,GI11<=300,FZ11<=300,FQ11<=300,FH11<=300,EY11<=300,EP11<=300,IN11<=2000),"A",IF(OR(ED11="",ED11="No",IB11>300,HS11>300,HJ11>300,HA11>300,GR11>300,GI11>300,FZ11>300,FQ11>300,FH11>300,EY11>300,EP11>300,IN11>2000),"R",""))))

BUT
as mentions
any blank cells in these
OR(IB11<=130,HS11<=130,HJ11<=130,HA11<=130,GR11<=130,GI11<=130,FZ11<=130,FQ11<=130,FH11<=130,EY11<=130,EP11<=130,IN11<800)
will be seen as zero and so TRUE and result in "G"

 

[felixstraube]

@etaf a real blank cell is considered zero, but a cell with a formula which return "" (empty string) is not considered zero. Its considered a string and all strings are greater than numbers.
All cells like EY8 return "" on certain condition.

@SaraWitch
Try this formula in B8 and copy down:

EA Placement Manager.xlsm
	B
8	A
EBC EA Placements

Cell Formulas
Range	Formula
B8	B8	=LET( d,N(HSTACK(EP8,EY8,FH8,FQ8,FZ8,GI8,GR8,HA8,HJ8,HS8,IB8,IN8)), upperLimit,{300,300,300,300,300,300,300,300,300,300,300,2000}, lowerLimit,{130,130,130,130,130,130,130,130,130,130,130,800}, IF(D8="","",IF(OR(ED8="",ED8="No",SUM((d>upperLimit)*1)),"R",IF(SUM((d<=upperLimit)*(d>lowerLimit)),"A","G"))) )

Named Ranges
Name	Refers To	Cells
'EBC EA Placements'!HBDetails	='EBC EA Placements'!$ED$8:$ED$1048576	B8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:B550	Expression	=$B8="xG"	text	NO
B8:B550	Expression	=$B8="xA"	text	NO
B8:B550	Expression	=$B8="xR"	text	NO