conditional formatting based on which number is min

[jkicker]

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

 

[DRSteele]

There is no way I can make head or tail out of that without more data. Can you please provide sample data together with expected outcome? Also, it would be easier to read your post if it were properly capitalised, especially the pronoun I, which should always be upper case.

The ROUNDDOWN functions is specifically designed to round towards the integer regardless of sign, or as MS puts it, "...number is rounded down to the left of the decimal point."

 

[jkicker]

I'm not sure what sample data you need. I'm asking about conditional formatting. Is there a way to use the properties of the MIN function, specifically the way that MIN takes arguments (ie MIN(number1,number2,number3)) and tell conditional formatting to act based on what number in the MIN function is the MIN. Can you tell conditional formatting, if argument 1 is the result then do some formatting? This way I don't have to write out the formulas, which would be 36 different rules over 12 cells. It would just be 3 rules copied in 12 cells.

 

[jkicker]

I think it boils down to this: is there a way for conditional formatting to look at which argument of a formula that chooses between those arguments is the result of the formula?
like, the if function chooses one of its arguments as a result, as do the min and max functions. so can we say if the result is argument1, do something.

 

[DRSteele]

Yes, there probably is a way. But I need sample data on which to experiment.

 

[jkicker]

this is A4	​	1​	5​	16​					1597​
	​	2​	10​	45​					1357​
	​	2​	10​	80​					1907​
	​	2​	8​	756​					497​
	​	150​	750​	1275​					4978​
	​	4​	40​	585​					460​
	​	8​	160​	80​					3378​
	​	15​	30​	60​					3022​
	​	3​	75​	40​					2178​
	​	17​	85​	44​
	​	16​	16​	1050​
	​	4​	400​	2500​

 

[DRSteele]

I hate to be a bother, but those numbers paste into Excel as some kind of text and try as I might I can't convert them to numbers!

Use the xl2bb functionality to provide us your sample data.

 

[Fluff]

They are unicode 8203, this removes them

Sub CleanSht()
With ActiveSheet.UsedRange
   .Replace What:=ChrW(8203), Replacement:="", LookAt:=xlPart
   .WrapText = False
   .Columns.AutoFit
End With
End Sub

That said, using XL2BB is a lot better

 

[jkicker]

i'm not sure if i can do that on this machine. i'll try tomorrow as i'm going home.

 

[DRSteele]

Ah, super!

This works too. I don't want to change my files to XLSM and mess around with VBA because I just don't understand it.

=SUBSTITUTE(C5,UNICHAR(8203),"")+0