Question description:
This user has given permission to use the problem statement for this
blog.
Hello,
I have a question regarding IF function in excel. I have a column of numbers and I want to highlight cells that are greater (or less than) a value of 5 when compared to the cells above and below it..
Please help!
Thankyou
Solved by C. D. in 33 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/10/2018 - 10:13
Hello, I understand that you need help with identifyin which cells are greater than or less than 5 when compared to the cells above and below it, right?
User
09/10/2018 - 10:13
Hello, Yes please!
Excelchat Expert
09/10/2018 - 10:13
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
09/10/2018 - 10:13
Are you able to provide a sample data using the sheet to the right or perhaps even upload your actual file so I can work on it?
User
09/10/2018 - 10:14
Yep 1 min
User
09/10/2018 - 10:15
I have added it to the excel spreadsheet (they are sound levels)
Excelchat Expert
09/10/2018 - 10:16
Okay, does your data really start in A1?
User
09/10/2018 - 10:16
Yes
Excelchat Expert
09/10/2018 - 10:16
I'm only asking because if it starts in A1 then the formula for A1 will have to be different than the rest.
User
09/10/2018 - 10:16
No sorry it will be a2
User
09/10/2018 - 10:16
A1 is titled Lmax in my personal excel
Excelchat Expert
09/10/2018 - 10:16
Okay, if it starts in A2 then the formula can be identical for all.
Excelchat Expert
09/10/2018 - 10:17
Okay, can you put a mark to those who we should be flagging?
Excelchat Expert
09/10/2018 - 10:17
Put a mark in column B.
User
09/10/2018 - 10:17
I would like to check all of the data?
Excelchat Expert
09/10/2018 - 10:17
Just a few would do so I'll have something to compare my results to.
User
09/10/2018 - 10:18
Oh I see
User
09/10/2018 - 10:19
I have done a few
Excelchat Expert
09/10/2018 - 10:20
Okay, so it has to be greater than or less than 5 compared to above AND below, right?
User
09/10/2018 - 10:21
Yes, just need to highlight cells that are different by a value of 5 when looking above and below it
Excelchat Expert
09/10/2018 - 10:21
I see. Thank you.
Excelchat Expert
09/10/2018 - 10:21
I'll be working something out using the sheet to the right. Please give me a few minutes to build the conditional formatting formulas.
User
09/10/2018 - 10:22
Thank you so much
Excelchat Expert
09/10/2018 - 10:22
What if it's exactly 5? Does it cound?
Excelchat Expert
09/10/2018 - 10:22
count*
User
09/10/2018 - 10:22
Yes it does
Excelchat Expert
09/10/2018 - 10:24
Alright, I have the formula. I'll be preparing the steps to apply the conditional formatting next.
Excelchat Expert
09/10/2018 - 10:24
Are your LMAX values in column A or is it in another column in your actual file?
User
09/10/2018 - 10:24
Great thanks alot
User
09/10/2018 - 10:25
Well they are but I can put them in column A it doesnt really matter
Excelchat Expert
09/10/2018 - 10:25
Okay. One moment.
Excelchat Expert
09/10/2018 - 10:26
Just finalizing the steps.
Excelchat Expert
09/10/2018 - 10:28
Please download this file.
[Uploaded an Excel file]
User
09/10/2018 - 10:28
Thank you!!
Excelchat Expert
09/10/2018 - 10:29
You will see 2 sheets. The first one is your sample data where the conditional formatting is applied. The second sheet is called the INSTRUCTIONS sheet where the actual steps to do the conditional formatting is written.
Excelchat Expert
09/10/2018 - 10:29
Note that if your data is anywhere else aside from column A then the entire steps will need to be updated to reflect that. This includes the formula.
User
09/10/2018 - 10:30
Okay cool thank you
Excelchat Expert
09/10/2018 - 10:30
Would there be anything else that I can help you with regards to the original question?
User
09/10/2018 - 10:31
Will I need to do the first 2 columns of equations to get the conditional formatting to work?
User
09/10/2018 - 10:32
Or can I just simply paste in the equation to the conditional formatting box
Excelchat Expert
09/10/2018 - 10:32
You don't. You can see the sheet I gave you does not have anything else aside from your numbers.
User
09/10/2018 - 10:32
Yeah i see
User
09/10/2018 - 10:32
Wow
Excelchat Expert
09/10/2018 - 10:32
So just follow the steps in the INSTRUCTIONS sheet and you'll be golden.
User
09/10/2018 - 10:32
Thanks alot man will use this website again!
Excelchat Expert
09/10/2018 - 10:32
Would you like to try and apply it to you file now while I'm with you?
User
09/10/2018 - 10:32
Okay
Excelchat Expert
09/10/2018 - 10:33
I can wait a while.
Excelchat Expert
09/10/2018 - 10:33
Alright, I'll be standing by in case you encounter any issues.
User
09/10/2018 - 10:36
for some reason it hasnt worked
User
09/10/2018 - 10:36
47.4
49.4
54.9
51.8
48.5
48.5
58.6
66.5
48.8
51
58.9
53.8
55.4
54.7
62.1
User
09/10/2018 - 10:36
It highlighted 48.5
Excelchat Expert
09/10/2018 - 10:36
Can you paste that information in the sheet to the right?
Excelchat Expert
09/10/2018 - 10:37
Paste those numbers in A2 and down.
Excelchat Expert
09/10/2018 - 10:38
I've tried it and it shouldn't. It should highlight 58.6 and 66.5 though.
Excelchat Expert
09/10/2018 - 10:38
This tells me that you might not have selected A2 when starting the steps.
Excelchat Expert
09/10/2018 - 10:39
If you selected A1 when following the steps instead of A2, the conditional formatting will be off by a cell so instead of highlighting 58.6 or the other, it highlights 48.5 instead.
User
09/10/2018 - 10:40
Okay give me a min and ill check
Excelchat Expert
09/10/2018 - 10:40
Delete your current applied conditional formatting using the Manage option then start from the top.
User
09/10/2018 - 10:44
Legend
User
09/10/2018 - 10:44
Its worked
User
09/10/2018 - 10:44
Thanks alot
Excelchat Expert
09/10/2018 - 10:44
That's awesome!
Excelchat Expert
09/10/2018 - 10:44
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
09/10/2018 - 10:44
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
09/10/2018 - 10:44
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
09/10/2018 - 10:45
definitely where do I get the option of giving you the feedback?
Excelchat Expert
09/10/2018 - 10:45
Once you end the session, you'll be given the option to.
Excelchat Expert
09/10/2018 - 10:45
Thank you. I'd really appreciate that. It would help me a lot.
User
09/10/2018 - 10:45
okay cool see ya man
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
