IF Command

[Mroy]

Hi All

How could I write an ‘If’ command that replaces values smaller than -5 in the previous column of data (J in this case) with a blank cell? I want then to delete all blank cells.
I am trying
=If (J2<-5, blank, J2)
=If (J2<-5, , J2)
=If (J2<-5, A , J2)

But with all of them, cells that are lower than -5 are populated with the #Name? error.
Could anybody please help?

M Roy

[Special-K]

Try

=(IF(J2<-5,"",J2)

[ihmha]

Try

Please Login or Register  to view this content.

[Mroy]

Hi Both,
Thanks a lot for your contributions,

Your formula does the trick however when I come to delete all the empty cells (using F5/Special/Blanks) then my empty cells are not selected. I copy and paste them all (using paste ‘Value’ command) so as to get rid of the F command within the cells, but still Excel doesn’t recognise the empty cells as 'empty'! it simply doesn't select them...

Any Suggestions?

I have attached an abbreviated file as the original file has about 90K rows! (It’s 10MB in size).

Cheers
Roy

[Sam Capricci]

your best bet is to do a copy, paste special >> values and this will replace the formulas with blanks, then you can do what you want by deleting the blank cells.

[Mroy]

Hi Sambo,

That is exactly what I am doing (special paste>> value), but when I come to select the blank cells (using F5/Special/blank) then empty cells are still not selected (you can try it on the spreadsheet I have attached).
I would genuinely appreciate any advice :o)
M. R.

[Sam Capricci]

well I'll admit to being a bit confused (but not a first for me ) but, for example, cell G2 along with column G in your sample data is/are empty. Col G is a simple delete and everything moves left. To delete cell G2 would mean you'd have to decide if you wanted to shift the surrounding cells either left or up which would alter your remaining information.
I agree there seems to be a "residue" in those cells after doing the paste special values as when I put my cursor in cell G2 and hold down shift/control and the down arrow it only goes down as far as the last row that held the previous formula, but if you then hit delete it now removes that "residue" and the same action will highlight the entire column to the bottom of the spreadsheet.
Maybe someone else knows how to get rid of that residue?

[Sam Capricci]

BTW, if the effort is to isolate rows, you could still apply a filter at the top of the data, even with the residue it still recognizes the blanks as blanks.

[newdoverman]

I cannot identify the character in question that is causing the problem but it appears to be something like a no width space that doesn't have an ANSI code (at least I couldn't find one).

[Mroy]

Well my dear friend, welcome to my miserable world of data collection :o)

The main file is a 3 year compilation of weather data in England (Newcastle upon Tyne). These values are ‘radiant’ temperatures logged by 4 sensors at 4 different orientations of a building.
Sometimes the transmitter fails to send the temperature and normally all my faulty data are below -5°C. So I want to get rid of all the faulty ones and analyse the rest. Each faulty data means that the entire row then is useless. So unless I have 4 correct readings, all else could be disposed of.
Using =(IF(J2<-5,"",J2) allows me to make all the incorrect cells into blank and then I can selected them and delete the entire rows. But as you correctly identified there remains some 'residues' in the cells that somehow stops Excel detecting them as empty.
I have attached half a year worth of data for anybody who wishes to comment. Remember that I have 3 years’ worth of these values…
WS download_1 May 2014_ For Forum.xlsx
Thanks Sambo for taking the trouble to investigate this …
M

[Sam Capricci]

Just playing around in your latest spreadsheet, a couple options come to mind. One, if all of your data that are below -5C, you could use a conditional format rule to color those cells, then a top row filter to filter on color then highlight all those rows and hit delete. Two, use your formula and then, again using the filter, filter on blanks, highlight those rows and right click delete them. Three, import into Access and using a couple formulas tag those rows for deleting or as garbage then produce reports excluding them.

[newdoverman]

Why don't you use the Filter available on the Data tab?

Click in your data, click the filter button and filters will appear on the headers of the columns. Choose the column to filter by, choose Number Filter, Less Than and enter -5. All values that meet that criteria will be the only values visible.

On the Home Tab click on Find & Select, Go To Special, Visible Cells Only. Click OK. Hit the delete key. (cut won't work)

Click on the Date column filter and Sort the dates in order. All blank rows will be gone.

[Mroy]

Hi Sambo:

I can highlight the target cells in red (using the conditional formatting).
When I apply the filter, I can't delete the target cells from there,

could you tell me what commands you use step by step?
Attached is my abridged formatted file !
Sensor data analysis_2 May 14.xlsx
Sincere thanks,

[Sam Capricci]

here it is back w/filter applied. after applying the filter to the top row, click the down arrow to filter, then before you get into the list you'll see filter by color, click that then click on the color you want. This will limit (as seen in what I saved) all those you might want to delete. Then go to the first row of the filtered data, highlight the row, then shift/ctrl down arrow to highlight all the rows, then right click and hit delete. They should all be gone.

[Mroy]

Hi Newdoverman
Absolutely marvellous. It did work and is really much simpler than the if command.
Thank you so very much indeed for clarifying this. Greatly appreciate your input…
M

[newdoverman]

You're welcome.

Thank you for the feedback. Glad to help.