Please guide to use one by one min & small functions with conditional formatting on non-adjacent cells i.e. each data row and column carry one blank row and column between these.
Using Min/Max on same cell concatenate. Let say you have 1 tru 5 on column A3:A7 A3->01 A4->02 A5->03 A6->04 A7->05 So, on Cell A1 write the following formula: =MIN(LC33:LC37)&"-"&MAX(LC33:LC37) A1 will display: 1-5
How do I highlight all the number that are in the bottom x%? Ex. 1,2,3,4,5,6,7,8,9,10 and I want to highlight the bottome 30% which should be 1,2,3. I tried using the PERCENTILE function but it only highlight the number at that percentile, so it only shows 3.
Hi Chris, Just watched the video on finding the lowest/ highest values for rows or columns w/ conditional formatting. My Excel sheet has 4 different distributor prices but the information is not contained in adjacent columns. I also have to consider that in order to find the lowest price from any of up to four different distributors, I have to review the data across many columns. What's the formula I should use if the columns are not contiguous? The cost of the product that I wish to find the lowest price are located in column F, L, R and X. So I would like to find the lowest price, for example on row 2, i.e., F2, L2, R2 and X2. Can you help me with this formula?
Hi Chris.I would like to learn how can ı write a formula to multiple 2 different minum amount which is set by conditional formattıng.What should be the formula to multiple 2 mın value automatıcally
Well explained, got my problem sorted. But I tried the same for Google Sheets and it doesn't work there. Can you give me any suggestions as to how I could get the Sheets to Highlight the Lowest Value. Thank you
@@ChrisMenardTraining oh thats great, looking forward for it. I tried to open the same excel sheet in google sheets and it had disabled the functions. thank you for your info
Let say I have the following numbers (C1=1, C2=3, C3=3, C4=4, C5=1, C6=6, C7=7, C8=9, C9=8). Which formula can I use to add the first six lowest of the numbers?
Use SumProduct and the Small function. =SUMPRODUCT(SMALL(C1:C9,{1,2,3,4,5,6})) Here is a screenshot drive.google.com/file/d/1psCclnSbg9ypOsWTt0EwwKuqqlsDLcsZ/view?usp=drivesdk
How to highlight the column C, range - on the based on max value of column A to min value of column B. A B C 50 60 1000 30 20 2000 [90] 50 (3000) 30 40 (1000) 20 [10] (5000) 40 80 80000 30 20 10000
Thanks Crhis! Is it possible to prevent excel from applying the cells formatting (background color) indicated on the conditonal formatting, when the cells have no value at all? While the cells have values, it works perfect, only highlights the intended ones, but if I wipe out the values in all the cell range, the background color chosen is applied. I'm using an older version of excel (2016) at this time. Thank in advance.
You can add another conditional formatting that says "when cell is empty" and put the color as "none" and put it above the one with the colored conditional formatting. Excel will read from top down therefore will take the "cell is empty" before the conditional formatting. I hope that makes sense, let me know if it doesn't.
![Наследники. Как живут сыновья Путина и Кабаевой | Фильм центра «Досье» | Putin's kids [English subs]](http://i.ytimg.com/vi/5Xrskn6lo7k/mqdefault.jpg)
You're the best guy teaching excel on YT
Thank you, Eden. I appreciate your comment.
Been searching and searching for a formula to accomplish this and your video helped me immensely! Thank you!!!!
Exactly what I was looking for. Good explanation. Thanks.
You're welcome!
Thank you, Chis. I was stuck on this problem, and you really helped. God bless.
You're welcome!
Thanks Chris, great explanation, very concise and straight to the point. Subscribed!!
Thanks, Ed! Much appreciated.
Thanks Chris. I really needed this today
You are so welcome
Superb guidance.
Glad it was helpful!
Thanks, Chris
Worked perfectly
I'm glad it worked! Thanks for the feedback. I appreciate it.
Thank you so much! Very helpful and the explanation is so clear that it's very easy to understand!
You're very welcome!
thank you! it helped me
Thank you Sir, you saved me a lot of work. Greetings from Mexico.
That was very helpful and easy to follow, thank you.
You're welcome!
Thanks!
Welcome!
Thank you sir & well explained and so useful for my task
You are most welcome
Thank you, Sir!
You are welcome.
Thanq sir
Welcome.
thanks a lot!
Welcome
Excellent...Thank you so much! 👍
You are welcome!
Great, THANKS!!
Glad you liked it. Thank you for the positive feedback.
thanks man for this tutorial
Happy to help
Lesson learned , thank you
You are welcome!
great, thank you very much
You are welcome!
Thanks
Welcome
Thanks, it works great.
In addition, is it possible to populate column heading name of the lowest value in column F?
Thank you
Thank you, super helpful and time saving for my task!!!
You're welcome!
Thank you super helpful
You're welcome!
Thank you🤍
This was great, exactly what I was looking for and worked like a charm. Thanks!
Could you do a video on comparing data against a column and highlight the greater values.
Great sir, it works! 👍
Thanks a lot!
You're welcome!
vera level christmas thatha...
thank u 😭
Welcome 😊
THANK YOU SO MUCHY
Welcome
Very well explained! Love it.
good chris
Thanks.
Please guide to use one by one min & small functions with conditional formatting on non-adjacent cells i.e. each data row and column carry one blank row and column between these.
Using Min/Max on same cell concatenate.
Let say you have 1 tru 5 on column A3:A7
A3->01
A4->02
A5->03
A6->04
A7->05
So, on Cell A1 write the following formula: =MIN(LC33:LC37)&"-"&MAX(LC33:LC37)
A1 will display:
1-5
Thank you very much!!! Much luck to you.
Hello, how can I highlight both max and min values in the same row?
Thanks !!
You are welcome
Hello, do you know what can I do if the cells are not together?
How do I highlight all the number that are in the bottom x%? Ex. 1,2,3,4,5,6,7,8,9,10 and I want to highlight the bottome 30% which should be 1,2,3. I tried using the PERCENTILE function but it only highlight the number at that percentile, so it only shows 3.
Hi Chris, Just watched the video on finding the lowest/ highest values for rows or columns w/ conditional formatting. My Excel sheet has 4 different distributor prices but the information is not contained in adjacent columns. I also have to consider that in order to find the lowest price from any of up to four different distributors, I have to review the data across many columns. What's the formula I should use if the columns are not contiguous? The cost of the product that I wish to find the lowest price are located in column F, L, R and X. So I would like to find the lowest price, for example on row 2, i.e., F2, L2, R2 and X2. Can you help me with this formula?
Did you find an answer for this? I’m looking for the same explanation. Thank you.
Hi Chris.I would like to learn how can ı write a formula to multiple 2 different minum amount which is set by conditional formattıng.What should be the formula to multiple 2 mın value automatıcally
Thanks for this Chris. What if there are more than 1 equal to the lowest number and I only want to highlight if there is just one lowest number?
Well explained, got my problem sorted. But I tried the same for Google Sheets and it doesn't work there. Can you give me any suggestions as to how I could get the Sheets to Highlight the Lowest Value. Thank you
I haven't tried in sheets. If I have time this weekend I'll give it a shot and see if I can get it to works.
@@ChrisMenardTraining oh thats great, looking forward for it. I tried to open the same excel sheet in google sheets and it had disabled the functions. thank you for your info
Ty
welcome.
Let say I have the following numbers (C1=1, C2=3, C3=3, C4=4, C5=1, C6=6, C7=7, C8=9, C9=8). Which formula can I use to add the first six lowest of the numbers?
Use SumProduct and the Small function. =SUMPRODUCT(SMALL(C1:C9,{1,2,3,4,5,6})) Here is a screenshot drive.google.com/file/d/1psCclnSbg9ypOsWTt0EwwKuqqlsDLcsZ/view?usp=drivesdk
What if i want to highlight the minimum value but not zero?
based on your example, what if you also state the value cannot equal to 0. how would you go about that?
For row minimum, how can i get the column H filled with respective Q1-Q4?
Kindly help to sort out my problem in excel ..
Can you also add the rule to Min, That value is > 0 , cause i got some blank cells that are selected , but there's no need to put any values there
Whats the formula for excluding zero?
How can i select the value that corresponds to min or max value in a column.
How to highlight the column C, range - on the based on max value of column A to min value of column B.
A B C
50 60 1000
30 20 2000
[90] 50 (3000)
30 40 (1000)
20 [10] (5000)
40 80 80000
30 20 10000
Is there a way I can use conditional formatting to automatically go from smallest to largest amount?
Hi,
How could I compare column values with reference column and highlight the least and the most differences?
Can you tell us how filter zero amount in this table, I want smallest amount next to zero.
I need it to count how many times the number is greater than 300 in the column.
=countif(range,">300") is the function to count numbers over 300.
@@ChrisMenardTraining Thanks.
Thank you. Please be my friend 😊
It is just highlighting max value of entire sheet
Hi sir I have a excel problem how to find high Low for a series of numbers and also high and low of the series divide by 2
Can u help
Thanks Crhis! Is it possible to prevent excel from applying the cells formatting (background color) indicated on the conditonal formatting, when the cells have no value at all? While the cells have values, it works perfect, only highlights the intended ones, but if I wipe out the values in all the cell range, the background color chosen is applied. I'm using an older version of excel (2016) at this time. Thank in advance.
You can add another conditional formatting that says "when cell is empty" and put the color as "none" and put it above the one with the colored conditional formatting. Excel will read from top down therefore will take the "cell is empty" before the conditional formatting. I hope that makes sense, let me know if it doesn't.
@@Rosebud7889 yes it worked!!! thanks a lot
What about if the data is not in a row and scattered in different cells
Conditional formatting is usually used if data is in a tabular format.
From Lahore Pakistan
Thank you!
@@ChrisMenardTraining Welcome!
At 02:28, the callout should read "I did NOT type"...
Why can't you not tried b2 to e5
It video no Zoom
can u please upload ur excel ty
Thanks
Thank you
You're welcome
How cal I get second to the lowest value?
Use the small function. =small(range,2)