i have the following formula:
=MIN(IF(($K$4-SUM($D$4:$D$15))/5>=0,ROUNDDOWN(($K$4-SUM($D$4:$D$15))/5,0),ROUNDUP(($K$4-SUM($D$4:$D$15))/5,0)),IF(($K$5-SUM($E$4:$E$15))/1.6>=0,ROUNDDOWN(($K$5-SUM($E$4:$E$15))/1.6,0),ROUNDUP(($K$5-SUM($E$4:$E$15))/1.6,0)),K6-C4-C6-C8)
That MIN formula has 3 numbers in it.
number1 = IF(($K$4-SUM($D$4:$D$15))/5>=0,ROUNDDOWN(($K$4-SUM($D$4:$D$15))/5,0),ROUNDUP(($K$4-SUM($D$4:$D$15))/5,0))
number2 = IF(($K$5-SUM($E$4:$E$15))/1.6>=0,ROUNDDOWN(($K$5-SUM($E$4:$E$15))/1.6,0),ROUNDUP(($K$5-SUM($E$4:$E$15))/1.6,0))
number3 = K6-C4-C6-C8
i also have 11 MORE min formulas, each with either 3 or 4 numbers. these numbers represent stuff, and the stuff they represent is all in the same order in each min formula (ie number1 represents time). is there a way to get conditional formatting to understand that i want to know which number (1,2, or 3) is my min? ideally without vba, but even with vba would be a learning experience i suppose.
i'm trying to avoid having to type out all that formula and would like to get it down to:
if(min(1,2,3))=1 then :red:
if(min(1,2,3))=2 then :blue:
else :yellow:
because that's something i can copy to all 12 cells. i feel like there is a way that i don't need to assign all that formula to 1, 2, and 3, since they all exist as parts of the min formula already.
p.s. why do negative numbers rounddown to the less negative number? ie -3.25 rounds down to -3 instead of -4. it's not round towards 0, it's rounddown! /rant
=MIN(IF(($K$4-SUM($D$4:$D$15))/5>=0,ROUNDDOWN(($K$4-SUM($D$4:$D$15))/5,0),ROUNDUP(($K$4-SUM($D$4:$D$15))/5,0)),IF(($K$5-SUM($E$4:$E$15))/1.6>=0,ROUNDDOWN(($K$5-SUM($E$4:$E$15))/1.6,0),ROUNDUP(($K$5-SUM($E$4:$E$15))/1.6,0)),K6-C4-C6-C8)
That MIN formula has 3 numbers in it.
number1 = IF(($K$4-SUM($D$4:$D$15))/5>=0,ROUNDDOWN(($K$4-SUM($D$4:$D$15))/5,0),ROUNDUP(($K$4-SUM($D$4:$D$15))/5,0))
number2 = IF(($K$5-SUM($E$4:$E$15))/1.6>=0,ROUNDDOWN(($K$5-SUM($E$4:$E$15))/1.6,0),ROUNDUP(($K$5-SUM($E$4:$E$15))/1.6,0))
number3 = K6-C4-C6-C8
i also have 11 MORE min formulas, each with either 3 or 4 numbers. these numbers represent stuff, and the stuff they represent is all in the same order in each min formula (ie number1 represents time). is there a way to get conditional formatting to understand that i want to know which number (1,2, or 3) is my min? ideally without vba, but even with vba would be a learning experience i suppose.
i'm trying to avoid having to type out all that formula and would like to get it down to:
if(min(1,2,3))=1 then :red:
if(min(1,2,3))=2 then :blue:
else :yellow:
because that's something i can copy to all 12 cells. i feel like there is a way that i don't need to assign all that formula to 1, 2, and 3, since they all exist as parts of the min formula already.
p.s. why do negative numbers rounddown to the less negative number? ie -3.25 rounds down to -3 instead of -4. it's not round towards 0, it's rounddown! /rant
Last edited: