Search and If/Or

[calliemulv]

Hello!


I need help with an automation I am trying to build through a formula. What I want to happen is in R3, the formula would look to Q3 as the first step. If Q3 says "GIA," then run formula 1; If Q3 says "COA," then look to F3. If F3 says "Off" in the field at all, run formula 2; If Q3 says "COA," then look to F3. If F3 says "Parents" in the field at all, run formula 3; If Q3 says "COA," then look to F3. If F3 does not say "Off" or "Parents," then run formula 4.


Basically:
R3 = If Q3 is GIA, "ROUND((P3+S3)/F3,2);" if Q3 is COA, look for Off in F3, if found "ROUND((P3+S3)/(F3+2982),2)" OR look for Parents in F3, if found "ROUND((P3+S3)/(F3+2838),2)" OR "ROUND((P3+S3)/(F3+2568),2)"


Formula 1 (GIA): ROUND((P3+S3)/F3,2)
Formula 2 (COA + Off): ROUND((P3+S3)/(F3+2982),2)
Formula 3 (COA + Parents): ROUND((P3+S3)/(F3+2838),2)
Formula 4 (COA + not "Off" or "Parents"): ROUND((P3+S3)/(F3+2568),2)


I have attached the spreadsheet I'm working with.


https://drive.google.com/file/d/1daCCUSFw7nDUZJh5El16yEF2CpHIhsjm/view?usp=sharing


Thank you!

 

[jtakw]

Hi,

Do you mean E3 rather than F3, cause F3 is a Number.

 

[calliemulv]

Oops - yes, that's correct: E3

Hi,

Do you mean E3 rather than F3, cause F3 is a Number.

 

[jtakw]

Thanks for clarifying.

Try either one of the 2 formulas below, they're essentially the same, just written differently.

Since some of your "drop down" list is from an external source, my testing was limited.

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(ISNUMBER(SEARCH("Off",E3)),2982,IF(ISNUMBER(SEARCH("Parents",E3)),2838,2568)))),2)

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(COUNTIF(E3,"*Off*"),2982,IF(COUNTIF(E3,"*Parents*"),2838,2568)))),2)

 

[calliemulv]

This is perfect - thank you!!

Thanks for clarifying.

Try either one of the 2 formulas below, they're essentially the same, just written differently.

Since some of your "drop down" list is from an external source, my testing was limited.

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(ISNUMBER(SEARCH("Off",E3)),2982,IF(ISNUMBER(SEARCH("Parents",E3)),2838,2568)))),2)

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(COUNTIF(E3,"*Off*"),2982,IF(COUNTIF(E3,"*Parents*"),2838,2568)))),2)

 

[jtakw]

You're welcome, welcome to the forum.