Condition within a range

[NicNi]

Hello,

I'm trying to write a formula to find if a column of results belong to one particular category. The results are shown underlined, cells filled in grey, red, or in black.

So far I've come up with this categories are
<A regular police, not underlined
A-B Underlined
B-C grey filled cell
C-D red filled cell
and <D black filled cell

=IF(Color=1,">D",IF(Color=3,"C-D",IF(Color=15,"B-C",IF(AND(Color=2,Underlined),"A-B","<A"))))

Color is =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,2,15))

and underlined is

=GET.CELL(22,OFFSET(INDIRECT("RC",FALSE),0,2,15))

I can't seem to be making it work for the cell range I'm looking for...

Any help is appreciated!

 

[StephenCrump]

Welcome to the Forum!

It looks like you're trying to test the colour / underline status of every cell in the column (height 15)?

Unfortunately, GET.CELL doesn't work that way. If you pass a range to GET.CELL, it looks only at the top left hand cell in that range.

You'll need to test each cell individually and aggregate the results, or switch to using a VBA function.

 

[NicNi]

Welcome to the Forum!

It looks like you're trying to test the colour / underline status of every cell in the column (height 15)?

Unfortunately, GET.CELL doesn't work that way. If you pass a range to GET.CELL, it looks only at the top left hand cell in that range.

You'll need to test each cell individually and aggregate the results, or switch to using a VBA function.

Aw shoot, I was trying to avoid using VBA.

Thank you for your answer though.

 

[StephenCrump]

In case the answer wasn't clear - you don't have to use VBA.

You can use GET.CELL from Excel, you just need to test cell by cell. You could do this in a helper column and hide that column, e.g. something like:

	A	B	C
1	Is range red?	FALSE	HIDE THIS
2
3			3
4			3
5			3
6			0
7			3
8			3
9			3
10
Sheet1

Cell Formulas
Range	Formula
B1	B1	=AND(C3:C9=3)
C3:C9	C3	=Color

Color: =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),,-1))

Note too that changing a cell's colour won't change the value of Color until the worksheet recalculates.