Extracting whole numbers (+ - 0.02 or 0.01) & ending in .25, .50, .75

[panyagak]

Dear ALL MrExcel

Formula for 1st problem is this:

=IF(ABS(ROUND(A1,0)-A1)<=0.02,"KEEP","DUMP").
(a) (The formula above also KEEPS 0.01s & 0.02s which I dont need). I need to incorporate an extra formula in it.

(b) I also need to extract all numbers ending in .25, .50 and .75 & need to incorporate it in the above formula:

ANY SINGLE EXTENDED FORMULA TO INCORPORATE THE 2 EXTRA CONDITIONS FOR SIMPLICITY?

Thanks.

Regards Patrick

 

[JoeMo]

Does this help?

=IF(MROUND(ABS(A1),0.25)=ABS(A1),A1,IF(ABS(ROUND(A1,0)=ABS(A1)),"KEEP","DUMP"))

 

[panyagak]

JoeM

Does it extract whole numbers (+ - 0.02 or 0.01) & all no.s ending in .25, .50, .75? you can test 1st on any set of values & expected results as: e.g

0.01 DUMP
0.02 DUMP
6.25 KEEP
34.50 KEEP
8.75 KEEP
9.00 KEEP
1.98 KEEP
5.99 KEEP.
9.26 DUMP

If it does, great: if it doesnt, rectify!!

Please refer to the earlier formula I provided for its improvement of the 2 extra conditions OR you can create a new one.

Cheers
Panyagak

 

[AliGW]

It's your job to check the suggested formula. If it isn't doing what you want, come back and give some more detail.

 

[panyagak]

AliGW

am not near my PC ! ! You can only tell me it has worked....& am satsfied!!

A good one

 

[AliGW]

It needs one small tweak to work as you have shown in your last post:

=IF(MROUND(ABS(A1),0.25)=ABS(A1),"KEEP",IF(ABS(ROUND(A1,0)=ABS(A1)),"KEEP","DUMP"))

Excel 2016 (Windows) 32 bit
	A	B	C
1	0.01	DUMP	DUMP
2	0.02	DUMP	DUMP
3	6.25	KEEP	KEEP
4	34.5	KEEP	KEEP
5	8.75	KEEP	KEEP
6	9	KEEP	KEEP
7	1.98	KEEP	DUMP
8	5.99	KEEP.	DUMP
9	9.26	DUMP	DUMP
Sheet1

Cell Formulas
Range	Formula
C1	=IF(MROUND(ABS(A1),0.25)=ABS(A1),"KEEP",IF(ABS(ROUND(A1,0)=ABS(A1)),"KEEP","DUMP"))

 

[panyagak]

AliGW

refer to Rows 7 & 8: a problem there

Thanks

 

[AliGW]

Why should those rows be KEEP? Explain how you have worked that out mathematically.

 

[panyagak]

AliGW

Any whole number + - 0.02 KEEP"
Eg: 10

(9.99, 9.98 10.01, 10.02) are all KEEP.

Its clear now?

Regards

 

[AliGW]

In your other thread on this I gave you this formula:

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

You need to combine that with what has been given in this thread thus:

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