| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Thanks.
"Biff" wrote: Hi! Use the shortened version of the formula! How it works: Even though you see column headers as letters, when referenced in functions columns evaluate as numbers. =COLUMN(A:A) evaluates to =COLUMN(1) =COLUMN(B:B) evaluates to =COLUMN(2) etc.. The range of columns where the data is located is: =COLUMN(BK:BW) or =COLUMN(63:75) 63 and 75 happen to be divisable by 3. The MOD function returns the remainder that would result in a division operation. 63 is divisable by 3 so there would be no remainder, or: =MOD(63,3) = 0 So, in the formula we want the columns in the range BK:BW or 63:75 where the MOD = 0 So we end up with this array: MOD(COLUMN(BK)=0 TRUE MOD(COLUMN(BL)=0 FALSE MOD(COLUMN(BM)=0 FALSE MOD(COLUMN(BN)=0 TRUE ... MOD(COLUMN(BW)=0 TRUE This is what we get in plain English: The value in cell X is the minimum if the cell in the column has a MOD of zero when divided by 3 and the value in the cell is greater than zero. Not my best explanation but I hope it helps! Biff -----Original Message----- Thank you, that worked perfectly, I needed to remove the $ before the 3's so when I copied it down the row changed but it does work perfect. How did you figure it out? What does MOD and -COLUMN do? Again thank you Joe "Biff" wrote: Hi! This is a real hack but it works: Formula is: =BK3=MIN(IF(MOD(COLUMN($BK$3:$BW$3)-COLUMN(OFFSET ($BK$3:$BW$3,,,1,1)),3)=0,IF ($BK$3:$BW$30,$BK$3:$BW$3))) You'll have to enter the cf in each cell individually. In other words, you can not select cells BK3,BN3,BQ3,BT3 and BW3 as a range and then enter the cf and expect the cell references to automatically adjust. You CAN enter the cf in one cell then CopyPaste SpecialFormats to the other cells. Biff -----Original Message----- That was the first attempt I made and it works except that it selects zero when it's the lowest. I'm trying to get this to work by ignoring or using greater than zero values and selecting the lowest value. Sometimes all cells have values and it works fine, it's the zeros that foul things up. TIA Joe "RagDyer" wrote: This works for me: Click in BN3, and start the conditional format. Formula Is: =BN3=MIN($BN$3,$BK$3,$BQ$3,$BT$3,$BW$3) Then, set your format, and <OK out. Now, with BN3 still selected, *double click* on the "Format Painter" icon (yellow paint brush), and navigate to, and click on, each of the cells in the formula. Then hit <Esc. And you're done! -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Joe Gieder" wrote in message news:65097F9C-B752-4E63-9587- ... Biff, The formula I need is something like this since the values I'm evaluating aren't side by side, they're every 3 columns. =$BN3=MIN(IF(OR($BK3,$BN3,$BQ3,$BT3,$BW3)0,OR ($BK3,$BN3,$BQ3,$BT3,$BW3))) My example does show them side by side but I didn't plan on running into zeros. Can this be done. with the formula like above it doesn't highlight anything. Sorry for the wrong example. Joe "Biff" wrote: Hi! If 0 was the lowest value it should have highlighted that. Formula is: =A3=MIN(IF($A$3:$D$30,$A$3:$D$3)) Biff -----Original Message----- Thank you. It works great until I ran into one problem, when 0 is in the equation it wont highlight anything. Is there a way to make it highlight greater than zero? TIA Again Joe "Joe Gieder" wrote: I'm trying to highlight the cell with the lowest value by using Conditional Format. Example.. A3 = 4 b3 = 7 c3 = 3 d3 = 10 I want to highlight C3 but I tried =MIN(K$3,$BN$3,$BQ$3,$BT$3) in the Formula Is part of Conditional Formating and it highlights all the values. Can This be done. TIA Again Joe . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional format | Excel Worksheet Functions | |||
| copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
| make a conditional format the default | Excel Discussion (Misc queries) | |||
| Conditional format | Excel Discussion (Misc queries) | |||
| Copying a conditional format | Excel Worksheet Functions | |||
Hybrid Mode
