Is this even possible?

[celauritz]

I have a list of six scores. Let's call them:

Score 1
Score 2
Score 3
Score 4
Score 5
Score 6

Each score can be given a value of 0 to 6. What I want to do is have another number (named MRC) multiply by a certain amount depending on the six scores.

For instance: If all the scores equal six, MRC is multiplied by 1.5
If five of the numbers equal six and the sixth doesn't, MRC is multiplied by 1.25
If four of the numbers equal six and the other two don't, MRC is multiplied by .75

Where it gets interesting is if all six numbers are five or greater, then MRC is multiplied by 1.25 and if five are five or greater, MRC is multiplied by .75

Thus, someone can and will have two different multipliers (or more). What I want to take is the largest of all the number and use it as the multiplier.

 

[lbass]

Can't follow this. Please use actual field names and show sample data including both/all fields.

-LB

 

[celauritz]

Six categories, all can be scored 0 to 6

Adherence
Conformance
Occupancy
Accuracy
Monitoring
Transition

There is a seventh number called MRC

I want to have a formula called "Multiplier" that multiplies the MRC by a certain number (figured below)

If all six categories are given six points, then MRC should be multiplied by 1.5
If any five of six categories are given six points, then MRC should be multiplied by 1.25 no matter what the sixth category scores
If any four of six categories are given six points, then MRC should be multiplied by .75 no matter what the fifth and sixth categories score
If any three of six categories are given six points, then MRC should be multiplied by .5 no matter what the other three categories score
If any two of six categories are given six points, then MRC should be multiplied by .25 no matter what the other four categories score
If any one of the six categories are given six points, then MRC should be multiplied by .1 no matter what the other five categories score.

However,

If all six categories get at least five points, then MRC should be multiplied by 1.25
If any five of the six categories score five points, then MRC should be multiplied by .75
If any four, then .5
If any three, then .25
If any two, then .1
else 0

then

If all six categories get at least four points, then MRC should be multiplied by .75
If any 5, then .5
If any four, then .25
If any three, then .1
else 0

If all six categories get at least three points, then MRC should be multiplied by .5
If any 5, then .25
If any 4, then .1
else 0

If all six categories get at least two points, then MRC should be multiplied by .25
If any 5, then .1
else 0

If all six categories get at least one point, then MRC should be multiplied by .1
else 0

I need it to take the highest multiplier. For example. If someone gets two fours, two fives and two sixes for his six scores, then, by reading above, he would get a multiplier of .25 for the sixes, .5 for having four score five or above, and .75 for having all six score four or above.

I would want this multiplier to be .75

 

[lbass]

The logic isn't the question. You still have not identified whether the categories are all instances of one field, e.g., {table.category} or whether these are different fields. Is there a separate score field? What does the data look like? Is MRC its own field? Show a little sample, please. Maybe it looks like:

{table.category} {table.score} {table.MRC}
Adherence 6 .2
Conformance 5 .2
Occupancy 3 .2
Accuracy 5 .2
Monitoring 2 .2
Transition 1 .2

-LB

 

[celauritz]

Score is a formula based on different fields in the table, so you are right about the first column, and basically the second column.

MRC is its own field. It is one number, but if it were linked to the other table by user, it would read like your info above.

 

[lbass]

Please respond directly to requests. I still don't know whether category is one field with six instances, for example. If score is a formula, you need to show the content of the formula. Please show some sample data.

-LB