Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.