It's best to start with sample data:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Ripe_qty
[/TD]
[TD]Rotten_qty
[/TD]
[TD]Color
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]red
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]golden
[/TD]
[/TR]
[TR]
[TD]banana
[/TD]
[TD]12
[/TD]
[TD]12
[/TD]
[TD]yellow
[/TD]
[/TR]
[TR]
[TD]orange
[/TD]
[TD][/TD]
[TD]31
[/TD]
[TD]orange
[/TD]
[/TR]
[TR]
[TD]apricot
[/TD]
[TD]14
[/TD]
[TD]13
[/TD]
[TD]pink
[/TD]
[/TR]
[TR]
[TD]apricot
[/TD]
[TD][/TD]
[TD][/TD]
[TD]brown
[/TD]
[/TR]
[TR]
[TD]melon
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]green
[/TD]
[/TR]
[TR]
[TD]strawberry
[/TD]
[TD][/TD]
[TD][/TD]
[TD]red
[/TD]
[/TR]
[TR]
[TD]plum
[/TD]
[TD]7
[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The results should answer the question: "for each type of fruit, what is the color of the lowest quantity, regardless of ripe/rotten status?"
----meaning, I don't really care whether the # is associated with Ripe or Rotten; I just need to know the color of the lowest number
----cells which are blank are truly blank. no values exist, but when encountered, some default value is set like '20'
Expected Results Table
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Color (formula needed here)
[/TD]
[TD]'manual answer' + notes
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]red
[/TD]
[TD]3 is the minimum value, and that matches red. the blank cell in the golden row is either ignored or replaced with the default value of 20
[/TD]
[/TR]
[TR]
[TD]banana
[/TD]
[TD]yellow
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]orange
[/TD]
[TD]orange
[/TD]
[TD]31 is the minimum value. Replacement of the empty cell with the default value of '20' would yield an incorrect answer based on the arbitrary value. Take the actual values when one is present.
[/TD]
[/TR]
[TR]
[TD]apricot
[/TD]
[TD]pink
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]melon[/TD]
[TD]green
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]strawberry
[/TD]
[TD]red
[/TD]
[TD]absent any values in either column.
[/TD]
[/TR]
[TR]
[TD]plum
[/TD]
[TD]{formula}?
[/TD]
[TD]7 and the answer provided by the formula will be 'zzNoColor'
[/TD]
[/TR]
</tbody>[/TABLE]
Any ideas or strategies?
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Ripe_qty
[/TD]
[TD]Rotten_qty
[/TD]
[TD]Color
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]red
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]golden
[/TD]
[/TR]
[TR]
[TD]banana
[/TD]
[TD]12
[/TD]
[TD]12
[/TD]
[TD]yellow
[/TD]
[/TR]
[TR]
[TD]orange
[/TD]
[TD][/TD]
[TD]31
[/TD]
[TD]orange
[/TD]
[/TR]
[TR]
[TD]apricot
[/TD]
[TD]14
[/TD]
[TD]13
[/TD]
[TD]pink
[/TD]
[/TR]
[TR]
[TD]apricot
[/TD]
[TD][/TD]
[TD][/TD]
[TD]brown
[/TD]
[/TR]
[TR]
[TD]melon
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]green
[/TD]
[/TR]
[TR]
[TD]strawberry
[/TD]
[TD][/TD]
[TD][/TD]
[TD]red
[/TD]
[/TR]
[TR]
[TD]plum
[/TD]
[TD]7
[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The results should answer the question: "for each type of fruit, what is the color of the lowest quantity, regardless of ripe/rotten status?"
----meaning, I don't really care whether the # is associated with Ripe or Rotten; I just need to know the color of the lowest number
----cells which are blank are truly blank. no values exist, but when encountered, some default value is set like '20'
Expected Results Table
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Color (formula needed here)
[/TD]
[TD]'manual answer' + notes
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]red
[/TD]
[TD]3 is the minimum value, and that matches red. the blank cell in the golden row is either ignored or replaced with the default value of 20
[/TD]
[/TR]
[TR]
[TD]banana
[/TD]
[TD]yellow
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]orange
[/TD]
[TD]orange
[/TD]
[TD]31 is the minimum value. Replacement of the empty cell with the default value of '20' would yield an incorrect answer based on the arbitrary value. Take the actual values when one is present.
[/TD]
[/TR]
[TR]
[TD]apricot
[/TD]
[TD]pink
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]melon[/TD]
[TD]green
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]strawberry
[/TD]
[TD]red
[/TD]
[TD]absent any values in either column.
[/TD]
[/TR]
[TR]
[TD]plum
[/TD]
[TD]{formula}?
[/TD]
[TD]7 and the answer provided by the formula will be 'zzNoColor'
[/TD]
[/TR]
</tbody>[/TABLE]
Any ideas or strategies?