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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
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