Suggestion on a better calcuatlion

[lostboyca]

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.

[BobWeaver]

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", "~"),

"~~","~")

[BobWeaver]

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

[djgogi]

How about

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

Dj

[Ugo DI LUCA]

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