|
Greater Than / Less Than Problem
Don't want to sound silly here, but I am actually having a problem with a formula that will allow me to count only values greater than 0 but less than 11. Is this Possible in 1 unique formula? Heres the situation: Within Column B6:B35, I want a count of any number that is greater than 0 but less than 11. I tried the following but it returns a 0: countif(B6:B35,"0<11") Thanks In Advance for any help here... -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
countif(B6:B35,"0<11")
One way, try: =COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: Don't want to sound silly here, but I am actually having a problem with a formula that will allow me to count only values greater than 0 but less than 11. Is this Possible in 1 unique formula? Heres the situation: Within Column B6:B35, I want a count of any number that is greater than 0 but less than 11. I tried the following but it returns a 0: countif(B6:B35,"0<11") Thanks In Advance for any help here... -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
I meant <11, not <=11!
Sorry, -- AP "Max" a écrit dans le message de news: ... countif(B6:B35,"0<11") One way, try: =COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: Don't want to sound silly here, but I am actually having a problem with a formula that will allow me to count only values greater than 0 but less than 11. Is this Possible in 1 unique formula? Heres the situation: Within Column B6:B35, I want a count of any number that is greater than 0 but less than 11. I tried the following but it returns a 0: countif(B6:B35,"0<11") Thanks In Advance for any help here... -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
My Goodness, what a wierd Formula... But YES! it definately works! Thanks Max, Much Appreciated! (I still dont understand the = value on the last formula, but it actually works well. You would assume it would contain a <= value) but who cares, IT WORKS! thanks..:) -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
One way I know to accomplish this is by placing a Zero (0) in any unused out of the way cell. Then copy and paste this formula in what ever cell you want the total in. =COUNTIF($B$6:$B$35,"Z1")+COUNTIF($B$6:$B$35,"<11 ") Z1 is the cell I chose to put the (0) in. Hope this helps. Ed -- patele ------------------------------------------------------------------------ patele's Profile: http://www.excelforum.com/member.php...o&userid=35849 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
Hi
As an alternative to Countif you could use the Sumproduct function =SUMPRODUCT(--(INT(B6:B35/12)=0),B6:B35) Taking the Integer of the numbers in your range divided by 12, will return 0 for all values below 12, and 1 for any values greater than 11. The first part of the test will therefore return True when less than or equal to 11, and False when greater than 11. The double unary minus -- coerces these True's to 1 and False's to 0. Sumproduct then multiplies each of the values in your range by 1 or 0 and sums the results where all values outside the range 0 to 11 will have been converted to 0. -- Regards Roger Govier "Max" wrote in message ... countif(B6:B35,"0<11") One way, try: =COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: Don't want to sound silly here, but I am actually having a problem with a formula that will allow me to count only values greater than 0 but less than 11. Is this Possible in 1 unique formula? Heres the situation: Within Column B6:B35, I want a count of any number that is greater than 0 but less than 11. I tried the following but it returns a 0: countif(B6:B35,"0<11") Thanks In Advance for any help here... -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
My apologies
Ardus' solution only appeared after posting mine (far easier logic) and I noticed his correction to <11. I too had misread your posting and assumed you wanted to include 11. My formula would need to be modified to use INT(B6:B35/11) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi As an alternative to Countif you could use the Sumproduct function =SUMPRODUCT(--(INT(B6:B35/12)=0),B6:B35) Taking the Integer of the numbers in your range divided by 12, will return 0 for all values below 12, and 1 for any values greater than 11. The first part of the test will therefore return True when less than or equal to 11, and False when greater than 11. The double unary minus -- coerces these True's to 1 and False's to 0. Sumproduct then multiplies each of the values in your range by 1 or 0 and sums the results where all values outside the range 0 to 11 will have been converted to 0. -- Regards Roger Govier "Max" wrote in message ... countif(B6:B35,"0<11") One way, try: =COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: Don't want to sound silly here, but I am actually having a problem with a formula that will allow me to count only values greater than 0 but less than 11. Is this Possible in 1 unique formula? Heres the situation: Within Column B6:B35, I want a count of any number that is greater than 0 but less than 11. I tried the following but it returns a 0: countif(B6:B35,"0<11") Thanks In Advance for any help here... -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
"Mhz" wrote:
My Goodness, what a weird Formula... But YES! it definitely works! Thanks Max, Much Appreciated! (I still dont understand the = value on the last formula, but it actually works well. You would assume it would contain a <= value) but who cares, IT WORKS! You're welcome. We're simply slicing off the part we don't want (=11) from the returns by the 1st countif: COUNTIF($B$6:$B$35,"0") via subtracting it with the 2nd one: COUNTIF($B$6:$B$35,"=11") (visualize it as a number line ..) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Greater Than / Less Than Problem
Thanks For All The Replies!! Roger, That is a Brain Digging Formula But surely Works..:) And Yes, Max Explained about the =11 Factor, (Cutting of anthing Above 11), WORKS GREAT! Thanks to all of you.. More than one way to get a good result, from simple to complex...GOOD DEAL! ;) -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
Greater Than / Less Than Problem
.. GOOD DEAL!
ay, that's what one always get around here <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Greater Than / Less Than Problem
Actually, your formula will count all of the values in the range, with the
values between 0 and 11 counted twice. "Z1" s/b ""&Z1 "patele" wrote: One way I know to accomplish this is by placing a Zero (0) in any unused out of the way cell. Then copy and paste this formula in what ever cell you want the total in. =COUNTIF($B$6:$B$35,"Z1")+COUNTIF($B$6:$B$35,"<11 ") Z1 is the cell I chose to put the (0) in. Hope this helps. Ed -- patele ------------------------------------------------------------------------ patele's Profile: http://www.excelforum.com/member.php...o&userid=35849 View this thread: http://www.excelforum.com/showthread...hreadid=561009 |
| All times are GMT +1. The time now is 05:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com