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

panyagak

Active Member
Joined
Feb 24, 2017
Messages
299
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Does this help?
Code:
=IF(MROUND(ABS(A1),0.25)=ABS(A1),A1,IF(ABS(ROUND(A1,0)=ABS(A1)),"KEEP","DUMP"))
 
Upvote 0
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
 
Last edited:
Upvote 0
It's your job to check the suggested formula. If it isn't doing what you want, come back and give some more detail.
 
Upvote 0
AliGW

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

A good one
 
Upvote 0
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
ABC
10.01DUMPDUMP
20.02DUMPDUMP
36.25KEEPKEEP
434.5KEEPKEEP
58.75KEEPKEEP
69KEEPKEEP
71.98KEEPDUMP
85.99KEEP.DUMP
99.26DUMPDUMP
Sheet1
Cell Formulas
RangeFormula
C1=IF(MROUND(ABS(A1),0.25)=ABS(A1),"KEEP",IF(ABS(ROUND(A1,0)=ABS(A1)),"KEEP","DUMP"))
 
Upvote 0
Why should those rows be KEEP? Explain how you have worked that out mathematically.
 
Upvote 0
AliGW

Any whole number + - 0.02 KEEP"
Eg: 10

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

Its clear now?

Regards
 
Upvote 0
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")))
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top