Re: Filter whole numbers with + - 0.02 or 0.01

[panyagak]

Re: Filter whole numbers with + - 0.02 or 0.01

Hi Joe4 & Happy Easter

Refer to the above title and below is your answer:
It works perfectly though 0.01s & 0.02s are also
"TRUE"- I dont need them.

Any way to lock them out by adjusting the code.

This formula will tell you which ones to Keep/
Dump (for an entry in cell A1):

Code:

=IF(ABS(ROUND(A1,0)-A1)<=0.02,"KEEP","DUMP")

Regards
Patrick

 

[AliGW]

Re: Filter whole numbers with + - 0.02 or 0.01

You're welcome!

 

[Aladin Akyurek]

Re: Filter whole numbers with + - 0.02 or 0.01

Aladin Akyurek

Please type in Excel
0.01
0.02
0.01
0.02
apply Joe4's formula, then refer to my problem!!

thanks

What are the desired results for these values?

 

[AliGW]

Re: Filter whole numbers with + - 0.02 or 0.01

They are all "Dump" as they are less than or equal to 0.02 (before any rounding has been applied). The OP seems to want to reject these first before performing the calculation. That's what I've built into my solution.

 

[panyagak]

Re: Filter whole numbers with + - 0.02 or 0.01

AliGW.
Great.

For Aladin Akyurek, please follow AliGW thread!!

Regards
Patrick

 

[panyagak]

Re: Filter whole numbers with + - 0.02 or 0.01

Hi Mrexcel

Can I "complicate" matters to the above formula?

Suppose I also need to also extract all numbers ending in .25, .50 and .75 & need to incorporate it in the above formula: something like this?????

=IF(MOD(A1,1)=0.25,A1,"") =IF(MOD(A1,1)=0.50,A1,"") =IF(MOD(A1,1)=0.75,A1,"")

NOT SURE HOW TO!!

Regards
Patrick

 

[AliGW]

Re: Filter whole numbers with + - 0.02 or 0.01

Deleted