challenge, in excel, i need to set a limit of 25K either up or down as criteria for showing value of cell?

Question

to set a limit of 25K either up/down for showing value of a cell, i have written the follwoing syntax but if the value of the cell is in the range 0f plus/ minus 25 it shows "false" how can i get a zero instead or empty cell. the syntax is as follows:
=IF(I2>0,IF(I2>25000,I2),IF(I2<0,IF(I2<-25000,I2)))

Answer 1

EDIT: updated code because old code overwrote values in cells. This code just changes the font color to white so that you won't see the values if the cells' background is white.



This is not possible just using a formula in Excel. You need to write code in Excel VBA. Here's the steps you need to follow:





1. Set macro security to medium so that you can run this code.



Tools->macro->security

Choose medium. This will let you choose whether or not to enable macros (programs) whenever opening an Excel file containing them. Then close Excel and reopen it.



2. Name the range of cells containing values you want to evaluate.



Select the range of cells. Then in the box that shows the cell address (i.e., E9), type in the name "datacolumn" without the double quotes (" "). Make sure that you repeat this step each time the size of your data range changes. If you have already named the range before, you need to first delete it by going to Insert->name->define and deleting "datacolumn".



3. Press Alt+F11 to open up the Visual Basic Editor

4. Create a module to copy the code into.



Insert->Module



5. Copy the following code into the box that appears. Make sure that if "Option Explicit" is in the box, that you copy the code BELOW "Option Explicit".



Sub ShowCellValues()

Dim cell As Range



For Each cell In Range("datacolumn")

If cell.Value < -25000 Or cell.Value > 25000 Then

cell.Font.ColorIndex = 2

End If

Next cell

End Sub



6. Click on the button that looks like the play button on a DVD remote control. It should be right above the box you copied the code into.

Answer 2

A lot depends on what you are wanting to do, and whether you're doing it over a range of values. The formula listed by T will work, but a macro would be an easier solution if you're computing it for a range of cells. If you're releasing the spreadsheet to the public, or tabulating results based on values within that range, simply changing the text color is not a sufficient answer.



If you want to put a different value (such as 0 or an *), you can use the same macro as suggested by clueless websurfer, except change the line



cell.Font.ColorIndex = 2



to



cell.Value = "*"

or cell.Value = 0



etc.



Good luck!

Answer 3

Are you saying that you want to put the number in the cell if it lies between -25000 and +25000, otherwise you want to put 0 in the cell? Or are you saying something else?



BTW, if you put some spaces in the sample code it won't get truncated. (use preview to check)

Answer 4

What I have done is use conditional formatting. Under Format>Conditional Formatting... you can set that if the value is greater than 25000 or less than -25000 then format it using White text (which essentially makes it disappear into the background).

Answer 5

=IF(I2>25000,I2,IF(I2<-25000,I2,0))

Answer 6

=IF(I1>0,IF(I1>25000,I1,0),

IF(I1<0,IF(I1<-25000,I1,0)))