I have this calculation which is a little sloppy and I was wondering if anyone can help me.

(If( tc1_xp_vcd = "Marginal", 1, 0)+If( tc2_xp_vcd =

"Marginal", 1, 0)+If( tc3_xp_vcd = "Marginal", 1, 0)+If(

tc4_xp_vcd = "Marginal", 1, 0)+If( tc5_xp_vcd =

"Marginal", 1, 0)+If( tc6_xp_vcd = "Marginal", 1, 0)+If(

tc7_xp_vcd = "Marginal", 1, 0)+If( tc8_xp_vcd =

"Marginal", 1, 0)+If( tc9_xp_vcd = "Marginal", 1, 0)+If( tc10_xp_vcd = "Marginal", 1, 0)+If( tc11_xp_vcd = "Marginal", 1, 0)+If( tc12_xp_vcd = "Marginal", 1, 0)+If( tc13_xp_vcd = "Marginal", 1, 0)+If( tc14_xp_vcd = "Marginal", 1, 0)+If( tc15_xp_vcd = "Marginal", 1, 0)+If( tc16_xp_vcd = "Marginal", 1, 0)+If( tc17_xp_vcd = "Marginal", 1, 0)+If( tc18_xp_vcd = "Marginal", 1, 0)+If( tc19_xp_vcd = "Marginal", 1, 0)+If( tc20_xp_vcd = "Marginal", 1, 0)+If( tc21_xp_vcd = "Marginal", 1, 0)+If( tc22_xp_vcd = "Marginal", 1, 0)+If( tc23_xp_vcd = "Marginal", 1, 0)+If( tc24_xp_vcd = "Marginal", 1, 0))/24

What I would like to do is that to make it calculate I would like calculate it with a wild card something like this tc*_xp_vcd = "Marginal", 1, 0 which the * represent a number from 1 - whatever so the formula won't be so long and much cleaner.

You could apply the following function to the text before you process it in your calculation. This will convert all one or two digit numbers into the string "~". So, you can then search for "tc~_xp_vcd"

Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(

Substitute(Substitute(Substitute(Substitute(Substitute(TheString,

"0", "~"),

"1", "~"),

"2", "~"),

"3", "~"),

"4", "~"),

"5", "~"),

"6", "~"),

"7", "~"),

"8", "~"),

"9", "~"),

"~~","~")

Oops, I just re-read your post. Is tc*_xp_vcd a field name? If so, there isn't much you can do.

How about

PatternCount(Field1 & " " & Field2 & " " & Field3 & ... & " " & Fieldn, "Marginal")/n

Dj

Hi,

I have the feeling that each boolean calc could also be used individually for other reasons.

Thus, I think a wildcard isn't what you need here.

I'd better very slightly modify your calc to have :

- 24 alternate calcs "BooleanN"

Case(your fieldN = "Marginal",1,0) and simply change your calc to :

- MarginalCheck = (Boolean1+Boolean2+...Boolean24)/24