Please help with TRICKY FORMULA

[sam.tidswell-norrish]

I have a worksheet with 30 columns across. In column A I want either a tick or a cross in each box to indicate whether all the columns have been filled in between columns B and AT.

Ideally it would be a tick if there are 4 or less blank columns on each row, a red cross if there are between 4-20 blank columns on each row and another symbol if there are 20+ blank fields.

Currently I am using this formula:

=LOOKUP(COLUMNS(B1:AT1)-COUNTA(B1:AT1),{0,1,25;"a","r","x"})

with webdings to illustrate the crosses/ticks etc. This one isn't working in terms of 1-4 blanks as a tick etc etc.

Thanks

 

[Jonmo1]

Just change the 1 to a 4..or 5...
Your definition has overlapping ranges...

it would be a tick if there are 4 or less blank columns on each row, a red cross if there are between 4-20 blank

Would 4 blanks be a tick (change to 5) or a red cross (change to 4) ?

=LOOKUP(COLUMNS(B1:AT1)-COUNTA(B1:AT1),{0,1,25;"a","r","x"})

=LOOKUP(COLUMNS(B1:AT1)-COUNTA(B1:AT1),{0,4,25;"a","r","x"})


Also, you can replace
COLUMNS(B1:AT1)-COUNTA(B1:AT1)
with
COUNTBLANK(B1:AT1)


Hope that helps.

 

[sam.tidswell-norrish]

Anyone?

 

[Smitty]

Please continue in your Original thread.