Formulas Question - Complex formulas using multiple factors

Question

Hello,
I'm dipping my toe into formulas - to date, I've used form rules to accomplish what I think formulas can, in a way that feels like the long way around (and requires you to actually open a record/save it, to fire the changes - whereas I believe formulas will just always be current with no manual intervention needed - please tell me if I am wrong).

My formula is complex with 4 choices - and each choice is a calculation of a couple of factors.
TL;DR: How do I factor in 4 choices that each use an&nbsp;AND function (how do I write "AND" in quickbase.... this is something I'm struggling to Google) - and/or is there a way to use an OR function, to get to each choice?

Detail:
I'm looking to say, "If this AND that, OR the other, then say option 1; if not, then if&nbsp;this2 AND that2, then say&nbsp;option 2; if not, then if&nbsp;this3 AND&nbsp;that3 AND&nbsp;not this other thing, then say,&nbsp;option 3"

How do I get the functionality of AND in this context? Alternatively, I think I could get to this if I have an&nbsp;OR function as well

If this still doesn't make sense the way I'm asking, here's further detail:
The field ultimately is, "What formal services are available in this county (across all hospitals)?" based on two data points - the county either has hospitals with my program, are hospitals with state-certified programs, both, or neither.

Prep:

I created formula fields to count the # of hospital records with my program; # of hospitals that are state-certified; # of "hospitals who are both", and # of hospitals with neither

I believe the formula should basically be saying,

If the county's # of hospitals with my program is greater than 0&nbsp;AND&nbsp;its # of hospitals with state programs is greater than 0,&nbsp;OR its # of hospitals with both is &gt;0, then "This county has Both." If not....
If the county&nbsp;# of hospitals with my program is 0&nbsp;AND&nbsp;its # of state programs is greater than 0, then say "This county has state certified programs".&nbsp;If not....
If the county # of hospitals with my program is greater than 0&nbsp;AND&nbsp;its # of state programs is 0, then "This county has my program."&nbsp;If not
Basically if everything else is zero, then "This county doesn't have a program".
------------------------------Christine Kirk------------------------------

markshnier__you · Answer

no problem, Quickbase formulas are similar to Excel but easier to write and read.&nbsp; Also you can add comments for our own sanity and you only tyopically need to have one IF statement as opposed to Excel where you need to have all those nested IFs.
IF(
([county's # of hospitals] &gt;&nbsp; 0 and&nbsp;[# of hospitals with state programs] &gt;0)&nbsp;&nbsp;
or
([# of hospitals with both]&nbsp; &gt;0, "This county has Both.",&nbsp;
[county # of hospitals with my program] =0 and [# of state programs] &gt; 0,&nbsp;
&nbsp; "This county has state certified programs",
[county # of hospitals with my program] &gt; 0 and [# of state programs]=0,&nbsp;
"This county has my program.",
&nbsp;"This county doesn't have a program".)
.
// by the way f you want to add a comment, then start a line with //

------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

christinekirk · Answer

Thank you Mark! You're always so quick to reply and I appreciate you!!

It looks like I might not have been far off the beaten path then. I'm still spitting out an error right off when I use your formula:

Also - when you mentioned adding a comment with // -- do you mean on this thread, or in the Quickbase? Wasn't sure I followed :).
------------------------------Christine Kirk------------------------------

christinekirk · Answer

Ok I think I've got it now actually. Ugh. It's just tricky, sometimes it wants a certain thing and I don't understand what it's asking for. A little practice and I'll get this one. For the life of me, I couldn't make "or" work. But I'm just going to add it as another option. "These criteria met = Choice A", "These other criteria met = Still Choice A".
------------------------------Christine Kirk------------------------------

markshnier__you · Answer

Yes, no problem just listing your conditions sequentially if that easier than use OR, even if a bit of repetition. &nbsp;The main issue is how stupid simple the formula is to read and understand. &nbsp;The goal is stupid simple.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

christinekirk · Answer

Okay deleting that whole extra reply now to simply say - NOW I finally have it all functioning properly haha. I think I must have also at some point made two duplicate sets of counter fields -- I've deleted the duplicate set, everything's operating cleanly off of just one set of counting fields. (I set the counters up initially for the form rules..... ages ago, and this is always part of the overall maintenance of your tables I feel like).

Lastly my hangup seems to have been selecting the wrong type of formula field (multi-choice text versus just text formula field).
------------------------------Christine Kirk------------------------------

Forum Discussion

Formulas Question - Complex formulas using multiple factors

5 Replies

Related Content

Formula URL - Edit then Add

Create a formula duration over 5 years

Looking for State abbreviation to full spelling formula

Formula question

Questions & Checklist setup

Recent Discussions

When the Expiration Date is On or Before Today, Change the Contract Status to Expired

Copied Field doesn't have the same usage as the original

Forms help

'Is Set' in Pipelines

Share your feedback on the new Qrew Discussions