8 Replies Latest reply on Nov 16, 2009 7:50 AM by raybaudi

# Calculation for Conditional Formating

### Title

Calculation for Conditional Formating

### Post

Using FMP10

I need help creating a formula comparing values in 20 different fields.  I want the lowest value out of all 20 fields to be in a green box.  Here is difficulty I am having, if any of the fields are empty, I do want the formula to include them.

So the formula has to evaluate the values in all 20 different fields, provided that there is data in the field, if and fields are empty or equal to zero, the values in those fields should not count.

If you have any questions, please let me know.

Any thoughts?

• ###### 1. Re: Calculation for Conditional Formating

You requirements are contadictory. In one sentence, you say you want the formula to include empty values. In another, you say you want the formula to omit empty values.

Use "Self = Min (list of fields)". If you want to include blank fields, put "0 + fieldName" for each field. If not, put "fieldName" for each field.

If true, then the value of the field is the lowest value. It is possible to have many identical lowest values.

• ###### 2. Re: Calculation for Conditional Formating
I am trying to have the lowest value in green.  If one or more of the fields is blank, it is going to through everything off.  I want to include all 20 fields, but if they are blank, I want the formula to leave them out.  Does that make sense?
• ###### 3. Re: Calculation for Conditional Formating

It does!

Use "Self = Min (list of fields) and not Isempty (Self)". If you had two fields named field1 and field two, the statement would look like this: Self = Min (field1; field2) and not Isempty (Self).

This will return true if and only if the field has information and is the lowest value.
• ###### 4. Re: Calculation for Conditional Formating
I used your formula and it is great.  Is there a way where I can get the formula to ignore values that are zero?
• ###### 5. Re: Calculation for Conditional Formating
Self = Min (if (field1 = 0; ""; field1); if (field2 = 0; ""; field2)) and not Isempty (Self)
• ###### 6. Re: Calculation for Conditional Formating

I have a question about the actual calculations that I am performing.  The equation is:

Quote1Quantity1Raw +( ( Quote1TotalSetUpCharge + Quote1ProductionSetUpCharge ) / QuoteOrderQuantity1)

How can I get the calculation to only perform the formula above if ALL fields have been entered?  Right now, all of the fields contain information except "Quote1Quantity1Raw" and I do not want the calculation to happen until information is entered for that field.  How do I get that to happen?

• ###### 7. Re: Calculation for Conditional Formating
An If statement that checks if any field is empty (use Isempty) is the best way to achieve this. If any field is empty, then return nothing. Otherwise, perform the calculation.
• ###### 8. Re: Calculation for Conditional Formating

If Quote1Quantity1Raw is a number field:

Case(
Quote1Quantity1Raw ; Quote1Quantity1Raw + ( Quote1TotalSetUpCharge + Quote1ProductionSetUpCharge ) / QuoteOrderQuantity1
)