Excel help !!

Hi guys,

I'm trying to filter out some of the date on my excel using the IF formula, but is not working.

I used the formula : =IF(E1<250,0,E1)
I basically want to filter out an data that is less than 250 BUT also more than 1000 - how do i fit this in to the same formula?

Thanks

(edited 8 years ago)

Reply 1

username738914

21

Original post by Retroandbeyond

Hi guys,

I'm trying to filter out some of the date on my excel using the IF formula, but is not working.

I used the formula : =IF(E1<250,0,E1)
I basically want to filter out an data that is less than 250 BUT also more than 1000 - how do i fit this in to the same formula?

Thanks

Use VBA.

Read through this link to set it up: http://euwern.com/794/excel-vba-setup-guide

Once in the VBA editor set up a program that iterates through each cell (is all your data in the E column?):

Sub dataCleanMacro()

Dim myDataRange As Range, Dim cell As Range

Set myDataRange = Range('E1 :colone:

34') (set it to whatever number of rows you need)

For Each cell In myDataRange
If cell.Value > 1000 Then
cell.Value = 0
ElseIf cell < 250 Then
cell.Value = 0
End If
Next cell

End Sub

Posted from TSR Mobile

Reply 2

davros

16

Original post by Retroandbeyond

Hi guys,

I'm trying to filter out some of the date on my excel using the IF formula, but is not working.

I used the formula : =IF(E1<250,0,E1)
I basically want to filter out an data that is less than 250 BUT also more than 1000 - how do i fit this in to the same formula?

Thanks

Only just spotted this thread, but your first port of call should be to see if Excel's standard Filter facility will do the job for you.

If you want a formula, look up the AND and OR functions (I think you want 'OR' here - you can't have a value that is both less than 250 and also more than 1000, that doesn't make sense!).

You can use something like IF(OR(E1<250, E1>1000),1,0) for example.

Use VBA as a last resort, but most things like this can be done via Autofilter or a formula / function combination :smile:

Excel help !!

Quick Reply

Related discussions

Latest

Trending

Trending

Articles for you