MIN(IF) formula error. Any alternate ways?

[Patricia_Hoffma]

I was wondering if anyone could help with my issue.
I am trying to get a value in field W MIN from an array of numbers (field: W Unique). But I only need to return the smallest number larger than a given number (field: Thermal MIN). I am using this formula in the field I want to return that number:

MIN(IF({W (from LED Performance Data) Rollup (from LED Type grouped by Output)} > {Thermal MIN (W)},{W (from LED Performance Data) Rollup (from LED Type grouped by Output)}))
(the formula is entered in the field column (drop-down arrow and select customize field type)

The same I need to achieve for the W MAX field comparing the values to the number in the Thermal MAX field.

However, it comes back with an Error! and I am not sure why?

Here is a screenshot of the columns and values.

Any help would be highly appreciated.

[TheTimeSavingCo]

Hi Patricia, I don’t think we’re able to do conditionals on each value of a rollup field, which should be why you’re getting an error

To achieve what you’re looking for, I would do the following:

In the LED Performance Data table, I would create a lookup field for the Thermal Min value called Thermal Min - Lookup. I would then create a formula field with the following formula callled Larger than Thermal Min:

IF(
  W > {Thermal Min - Lookup},
  W
)

This field now displays the W value only if it’s larger than Thermal Min - Lookup.

In the original table, I would now create a rollup field of Larger than Thermal Min, which should display the smallest number larger than Thermal Min for that record.

I would repeat this for the W Max field

I’ve put together a working version here. To view the formulas, duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

[Patricia_Hoffma]

Hi @Adam_TheTimeSavingCo, thanks so much for your help. I absolutely appreciate your time and detailed answer. I can see what you mean and it all makes sense. However when I follow your steps I get an error again. I assume it is because the LED table is not directly linked to any products. There is another table LED Type grouped by Output in between, which takes a group of records from the LED table and links those to a product in the product table. Can you solution be achieved also when the field is a rollup with many values? Hope this makes sense? Thanks again for your effort.

[TheTimeSavingCo]

Hi Patricia, I’m afraid I’m having a hard time following; once we get into the intricacies of base set up it’s pretty hard to visualize.

Is there any chance you could duplicate your base, delete all your records, and then private message me a link? I would then be able to create a working copy for you within your base setup

If not, would you be able to provide screenshots of the relevant tables?

[Patricia_Hoffma]

@Adam_TheTimeSavingCo I can’t thank you enough for taking your time to find the solution to this problem. Without a script it wouldn’t be possible and your script works like a charm. I am stoked about this community here. Very very helpful!!