We are trying to add a formula that if one room is available even though others are not it should still says “Available”. If one room says Becoming Available but there’s no room that is available then it should say “Becoming Available”. If all rooms are rented then it should say “Rented”

Hi,

i would suggest change a capital letter in second option, at least write “Becoming available”, because your formula "find word Available, but not in “Becoming Available” will be much harder.

IF(FIND(‘Avail’,x)>0, ‘Available’, IF(FIND(‘Becom’,x)>0, ‘Becoming…’, ‘Rented’)) - is a simple way . x - your search field, in your case, it’s lookup, so you should put something like CONCATENATE(field) instead of just field name, to convert array to string.

the hard way, if you can’t change values, may be something like

LEN(SUBSTITUTE(field,‘Becoming’,’’))-LEN(SUBSTITUTE(field,‘Availab’,’’))

result will be +1 for each ‘Becoming Available’, but -7 for each ‘Available’

you can compare it to zero or do like

SWITCH(

ROUNDUP(

(LEN(SUBSTITUTE(field,‘Becoming’,’’))-LEN(SUBSTITUTE(field,‘Availab’,’’))/99),0)

,-1,‘Available’

,1,‘Becoming’

,0,‘Rented’)

again, don’t forget about CONCATENATE(field)

I am not good with Airtable so how do I concatenate?

Concatenate is a formula to join multiple values to a single string.

Problem with using lookup and some other fields in formula - it’s actually an array, not a single value. Even if it’s just a single value or nothing, it’s still an array of 1 or 0 elements.

When you link your record to some others, lookups will show content of some field in a linked records, like [“Available”, “Available”, “Rented”] and formula may count it in a wrong way. So, you should use CONCATENATE({Occupied/Vacant (from…)} ) in you case instead of just using {Occupied/Vacant (from…)}, it will turn value to “Available, Available, Rented” so formula will work with it in a right way.

In short, rename your {Occupied/Vacant…} field to “field”.

Put formula . Indeed, i spotted some mistakes in first, each “Becoming Available” will add -1, and each “Available” +7, so it will not work for 7*Becoming+1*Available, will show “Rented”, so you should update it when you have more that 7 rooms in a record.

That one is checked:

```
SWITCH(
ROUNDUP(
(
(
LEN(SUBSTITUTE(CONCATENATE(field),'Becoming',''))-LEN(SUBSTITUTE(CONCATENATE(field),'Availab',''))
)
/9)
,0)
,-1,'Becoming'
,1,'Available'
,0,'Rented')
```

and ensure it works.

rename the field back (formula will auto-update)

Don’t worry about it. I have fixed it.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.