Countif Decimal is within a range
Hello,
I have a list of almost 200 numbers, all of them to the nearest hundredth point.
What I am trying to accomplish is to do a CountIf(Decimal points is between .98 and .02) regardless of what the whole number is.
I keep getting and answer of 0
Example
Please Login or Register to view this content.
5.00
6.45
7.86
3.98
2.99
4.01
12.02
8.24
Should = 5
Any Assistance would be appreciated!
Last edited by Bryan.Pagenkopf; 10-24-2018 at 01:18 PM. Reason: New Example File
first stab which isnt elegant
=SUMPRODUCT((MOD(A1:A8*100,100)<=2)*(MOD(A1:A8*100,100)>=0))+SUMPRODUCT((MOD(A1:A8*100,100)>=98)*1)
or perhaps
=SUMPRODUCT((MOD(A1:A8*100-98,100)<=4)*1)
Last edited by davsth; 10-24-2018 at 11:27 AM.
Please try
=SUMPRODUCT((MOD(E18:E200-0.02,1)<=0.96)*(E18:E200<>""))
Davsth and Bo_Ry,
Thanks, those were close but it's still not hitting the proper end value. I attached an Example File for reference.
Thank you for the help!
You've got rounding error (i.e. displayed number isn't what's stored, should be rounded to nearest 2nd decimal). Something like...
=SUMPRODUCT((ROUND(B3:B367,2)<=0.02)+(ROUND(B3:B367,2)>=0.98))
Or change B column formula to...
=Round(A3/$A$2,2)
And use simple COUNTIF with addition.
COUNTIF(B3:B367,"<=0.02")+COUNTIF(B3:B367,">=0.98")
Last edited by CK76; 10-24-2018 at 12:06 PM.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
=sumproduct(--(abs(round(b3:b367, 2) - round(b3:b367, 0)) <= 0.02))
Entia non sunt multiplicanda sine necessitate
shg,
That worked on my example file without issue! When I added it into my actual document I am getting a "#value" error.
=SUMPRODUCT(--(ABS(ROUND(E18:E200, 2) - ROUND(E18:E200, 0)) <= 0.02))
Any Ideas Why?
Thank you again!
No idea why.
Maybe your numbers are text masquerading as numbers?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
They are set as Numbers in the Cells. I do have them in the Cell via a VLOOKUP function but even the source Cells are set as numbers.
I've Uploaded a new Example File
Please try
=SUMPRODUCT(--(MOD(ROUND(B3:B367+0.02,2),1)<=0.04))
It's floating point issue.
Though @Bo_Ry's formula is more efficient, below would work as well.
=SUMPRODUCT((ROUND(E3:E367-TRUNC(E3:E367),2)<=0.02)+(ROUND(E3:E367-TRUNC(E3:E367),2)>=0.98))
I keep getting the #VALUE
Capture3.PNG
Bryan
Can you upload a sample file with the formulas that are giving you the error?
If posting code please use code tags, see here.
Norie, I can't attach my file but I did display the formulas used just above each #value error result in the picture above.
Last edited by AliGW; 10-25-2018 at 12:34 PM. Reason: Unnecessary quotation removed.
Yes, you can attach your file.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks