Hi All
I was trying to work on a formula which determines if any decimal exist in the given range.
say A1=5.00 A2=5.32 A3=9.64 A4="" A5=0.0, it should return me false as thers decimal in A2 and A3.
=IF(SUMPRODUCT(A1:A5/ROUND(A1:A5,0))/COUNTIF(A1:A5,">=0")<>1,FALSE,TRUE)
this formula works if there's no blank or zeros in the range, how to modify to exclude zeros and blanks?
Thanks in advance
=SUMPRODUCT(--(MOD(A1:A5,1)>0))
Try this
or
=SUMPRODUCT(--(MOD(A1:A6,1)>0))>0
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Try htis array formula (set with CTRL-SHIFT-ENTER)
=COUNTA(A1:A5)=SUM(IF(ISNUMBER(A1:A5),IF(ROUND(A1:A5,0)-A1:A5=0,1,0)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks a lottt
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks