I am looking for a way to color the lowest number in a group, but some of those numbers are first divided by two.
2 2
3 3
Say I am assembling bicylces and row 1 = frames, row 2 = wheels. I want to show what I run out of first when I want to build bikes. I have 2 frames, 3 wheels so I want to color the cells showing a 3 in red.
only that formula does not work, is there a way to have this work?
edit: removed formula as it was incorrect and caused confusion
Last edited by TtNl; 09-29-2022 at 08:51 AM. Reason: remove formula
You need to use two separate conditional formatting rules - it is unclear if your data is in rows (as shown) or in columns (as your formula implies)
Anyways, the first CF (applied to A1:A2) is
=A1=MIN($A$1:$A$2,$B$1:$B$2/2)
The second CF (applied to B1:B2) is
=B1/2=MIN($A$1:$A$2,$B$1:$B$2/2)
Or for A1:B1
=A1=MIN($A$1:$B$1,$A$2:$B$2/2)
The second CF (applied to A2:B2) is
=A2/2=MIN($A$1:$B$1,$A$2:$B$2/2)
Bernie Deitrick
Excel MVP 2000-2010
I messed around with this a bit more and I'm sure the answer is in here, I just can't get it to work.
I included a small example excel in the original post, would be very grateful if someone could make that work
Please enter in column A the numbers needed per bike and try this formula and copy across:Formula:
Please Login or Register to view this content.
Hi Hans,
I understand what you did but when I use that formula you typed, I get a "value" error message. Even clicking inside it and then pressing enter without changing anything already gives the error.
In the end it doesn't matter though as I wasn't looking for a solution to calculate the total number of bikes that are made, I was looking for a way to color the cell(s) that are the first resource you run out of when producing bikes. See the red cells in my example. I am looking for a way to conditionally format the cells so that the red colored cells are colored automatically.
I'm pretty sure Bernie got my question right but I can't get the solution to work.
Have you checked that all fields in the range are numeric or empty?
And did you enter in the first column the numbers needed per bike (only the number without text)?
Or add the workbook that contains the error
Last edited by HansDouwe; 09-29-2022 at 11:42 AM.
I have since learned that the {} brackets that I see in your workfile are due to us working in different excel versions and are related to how array formulas changed and the use of Control Shift Enter.
Still, the question I had remains. Looking to color the cells that I now colored in manually with conditional formatting.
Please try this CF-rule applies to $C$3:$H$8:Formula:Please Login or Register to view this content.
Awesome, thats exactly what I was looking for, thanks so much!
You're welcome. Thanks for the feedback. I'm glad the formula works the way you want it to.
Please consider adding reputation to the answers of any helpers you think deserve.
If you want, click * add reputation at the bottom left of the answer(s)
Thx for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks