Rounding off "Quagmire"!

[panyagak]

Hi MrExcel

Am scratching my head on how to "further restrict for exactness" the numbers in my data analysis, for which have been using Joe4's formula as: =IF(ABS(ROUND(A1,0)-
A1)<=0.01,"KEEP","DUMP").

I need to go a step further as follows:
Lock out any number THAT IS NOT FOLLOWED BY 00 AFTER THE DECIMAL PLACE (.00.....) BEFORE ROUNDING. (All numbers are eventually rounded to 2 d.p. & filtered)

Example 1:
19.012432 = 19.01 DUMP
19.005433 = 19.01 KEEP

Example 2:
1.011234 = 1.01 DUMP
1.009343 = 1.01 KEEP.
Etc etc


The "Joe4's formula above" takes ALL as "KEEP".

Any tweak to it? OR a separate formula is needed?

Thank you.

Patrick.

 

[kvsrinivasamurthy]

Re: Extracting specific numbers

Try this
=ISNUMBER(FIND(".00",A1))

 

[philwojo]

I am going to agree with Steve, I put your formula and your data in to a sheet and I get, just as you listed, Dump, Keep, Dump, Keep.

So that formula does NOT produce all "Keep" results.

Also, Panyagak, Steve is trying to get you to explain your request further, not trying to make a joke.


But from what I read in your post #1 , I think you are wanting to first check the values in Column-A and if they don't have a #.00#### and then do something with those numbers. So in your examples the 19.012432 and the 1.011234 would have something done to them because they don't meet your new criteria of having two zeros after the decimal place, is that correct?

But for your other two values, the 19.005433 and the 1.009343 you would analysis those using the formula you posted and get the result of "Keep" in both those cases.

So as Steve has asked, can you explain further what you want?

 

[philwojo]

If I am following you right, maybe this will work for you:

=IF((MID(A1,FIND(".",A1)+1,2))<>"00","BAD",IF(ABS(ROUND(A1,0)-A1)<=0.01,"KEEP","DUMP"))

I don't know what you want to do or say if it finds that it has something other than "00" after the decimal, but for now I put in the word bad, feel free to update that, or post what you are wanting if this isn't doing what you are expecting.

Phil

 

[Fluff]

Re: Extracting specific numbers

@panyagak

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
I have merged both threads

 

[panyagak]

steve the fish

That joke is right there: in your WRITTEN WORDS!!

Meanwhile, am reviewing bosco_yip & Vbagreenhorn1 "non-jokes"!!!

 

[steve the fish]

Id rather not retaliate. Ill leave people to make their own mind up whether you deserve any help.

 

[panyagak]

Re: Extracting specific numbers

Fluff

Most obliged:

I felt I needed to exclude critical info. in the original thread & the new thread actually has positive responses.

Thanks

 

[Fluff]

Re: Extracting specific numbers

You also had a positive response to original thread.
I tend to agree with with steve the fish. The formula you supplied gives the result that you showed.
If members cannot understand your requirements, it maybe that you have not explained yourself clearly.
In future, please do not start insulting people that are trying to help you.

 

[panyagak]

Re: Extracting specific numbers

Vbagreenhorn1

Thanks, it works

 

[panyagak]

Thanks ALL

All except 1 ANSWERS supplied ARE CORRECT!!

Thank you & SORRY FOR ANY "UNSAVOURY EXCHANGES".....take them lightly