Excel Functions, HELLLP, PLZ - Printable Version
-Shoutbox (https://shoutbox.menthix.net)
+-- Forum: MsgHelp Archive (/forumdisplay.php?fid=58)
+--- Forum: Skype & Technology (/forumdisplay.php?fid=9)
+---- Forum: Tech Talk (/forumdisplay.php?fid=17)
+----- Thread: Excel Functions, HELLLP, PLZ (/showthread.php?tid=39388)
Excel Functions, HELLLP, PLZ by M73A on 03-01-2005 at 10:08 PM
For The Functions formulae how do u specify something like =countif (a1:a55,BETWEEN 8 AND 10)
i cant seem to work it out and its really starting to bug me now
thanks.
RE: Excel Functions by Joa on 03-01-2005 at 11:03 PM
i am not sure but..
=countif(A1:A55,">8")-countif(A1:A55,">10")
??? 
i'm not sure if this will work i havent used excel in a long long while..
i'm sure it will count the # of values between 8 and 10 in the range you specified though...
EDIT: let me know if it worked 
RE: Excel Functions, HELLLP, PLZ by M73A on 03-01-2005 at 11:19 PM
hmm good idea,
it adds up the data in the ones that fit the specified countif criteria. hmmm this is confusing, why cant they just have it so you can say between8and10, lol
RE: Excel Functions, HELLLP, PLZ by Joa on 03-01-2005 at 11:30 PM
lol, i know, it pissed me off before too. 
i think there is a shorter formula for this though, but i cant remember it 
RE: Excel Functions, HELLLP, PLZ by M73A on 03-01-2005 at 11:31 PM
lol. ok, thansk anyway. hmmm could ask my ict teacher got that tomorrow, normally tell him the bloody answers is the only problem.
RE: Excel Functions by CookieRevised on 03-01-2005 at 11:44 PM
quote:What do you mean with that?
Originally posted by may73alliance
=countif (a1:a55,BETWEEN 8 AND 10)
Do you mean:
- add every cell up from A1 till A55
- check if the total is between 8 and 10
=> =IF(AND(SUM(A1:A55)>8;SUM(A1:A55)<10);"in between";"not ok")
Or do you mean:
- add every cell up from A1 till A55 but only if the cell is between 8 and 10?
=> In this case you need a matrix formula. This is entered a bit different then useuall. Also, this is not commonly known/used!
The formula:
=SUM(IF((A1:A55>8)*(A1:A55<10);A1:A55))
But instead of pressing the useuall <ENTER> after you've typed it, you need to press <CTRL><SHIFT><ENTER>.
If you do this you'll see that Excel will add "{" and "}" around the whole formula. This makes it a matrix formula; the region(s) will depict every element of the matrix separately instead of taking the whole matrix/region. In the formula textbox you'll see:
{=SUM(IF((A1:A55>8)*(A1:A55<10);A1:A55))}
Note: Do NOT add the "{" and "}" manually, it will not work! You need to press <CTRL><SHIFT><ENTER>.
As soon as you edit the formula again, the "{" and "}" will automatically be removed, so you need to press <CTRL><SHIFT><ENTER> again after the edit.
Note: the above matrix formula can be achieved with the normal formula:
=SUMIF(A1:A55;">8")-SUMIF(A1:A55;">10")
(this method will not work in all cases. But for this case it does)
EDIT:
Oh... well... I didn't had a look at the thread again before posting (there wasn't any post yet when I started to write)
I forgot that COUNTIF is a valid English Excel formula and didn't linked it to the Dutch formula AANTAL, so I didn't knew what you meant
... So yes, what Z_Joanna_ZZZ told ya was almost correct:=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">=10")
Notes:
* there is no "shorter" normal formula for this. Unless you make it into a matrix formula like explained above:
{=COUNT(IF((A1:A55>8)*(A1:A55<10);A1:A55))}
* if you meant x should be bigger then 8 and smaller or equal then 10, you must do:
=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">10")
or even:
=INDEX(INTERVAL(A1:A55;{8;10});2)
or as a matrix formula:
{=COUNT(IF((A1:A55>8)*(A1:A55<=10);A1:A55))}
PS: pressing F1 when you're cursor is on a formula can help a lot
RE: Excel Functions, HELLLP, PLZ by M73A on 03-02-2005 at 07:40 AM
Woah thanks cookie. ive tryed all of them an i get an error saying
What i am trying to do is count how many blocks have a number between 8 and 10 on them in a specific range.
thanks.
RE: Excel Functions, HELLLP, PLZ by The_Thief on 03-02-2005 at 07:39 PM
try http://support.microsoft.com they have a great database for excel
RE: Excel Functions, HELLLP, PLZ by M73A on 03-02-2005 at 08:09 PM
oooo, thanks. *on that now*
EDIT: ive got to http://support.microsoft.com/default.aspx/ph/2512?sid=202 but i cant find where it would be. Is there a like technical term for what i'm looking for that i can search for? thanks.
RE: Excel Functions, HELLLP, PLZ by saralk on 03-02-2005 at 08:22 PM
ok, i just made a simple formula now, it works for me, but its not inclusive
=COUNTIF(A1:H1, ">8")-COUNTIF(A1:H1, "<10")
RE: Excel Functions, HELLLP, PLZ by The_Thief on 03-02-2005 at 08:27 PM
Try doing a search for excel formulas or something along that line.
RE: Excel Functions, HELLLP, PLZ by M73A on 03-02-2005 at 09:49 PM
Cheers Saralk the formulae actually gives a result but not the right result. lolololol. hmmmm isnt it possible to do =COUNTIF(A1:H1, "8to10") sort of thing.
RE: RE: Excel Functions, HELLLP, PLZ by CookieRevised on 03-02-2005 at 11:44 PM
quote:Make 100% sure that you typed the formula correctly
Originally posted by may73alliance
Woah thanks cookie. ive tryed all of them an i get an error saying "the formula you typed contains an error"
Also note that the formula given is in English... Excel is a language dependant application, this means that the formula's are different in different languages.
eg: the English Excel formula SUM() would be SOM() in Dutch Excel, etc...
quote:Like first said by Z_Joanna_ZZZ and corrected by me:
Originally posted by may73alliance
What i am trying to do is count how many blocks have a number between 8 and 10 on them in a specific range.
* get number of cells in the range A1:A55 which have a value between 8 and 10 (thus excl. 8 and 10):
=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">=10")
* get number of cells in the range A1:A55 which have a value from 8 to 10 (thus incl. 8 and 10):
=COUNTIF(A1:A55;">=8")-COUNTIF(A1:A55;">10")
quote:Excel comes with a very extensive helpfile already, pressing F1 is all it takes
Originally posted by serpico
try http://support.microsoft.com they have a great database for excel
quote:That's wrong and wont give the correct result, it should be ">", not "<". And if you want to make it between 8 and 10 (thus not including 8 and 10), it should even be ">=10"
Originally posted by saralk
ok, i just made a simple formula now, it works for me, but its not inclusive
=COUNTIF(A1:H1, ">8")-COUNTIF(A1:H1, "<10")
Besides, the (almost correct) formula was already given in the second post of this thread (and again in my first post)
quote:read my first post in this thread?
Originally posted by may73alliance
Cheers Saralk the formulae actually gives a result but not the right result. lolololol. hmmmm isnt it possible to do =COUNTIF(A1:H1, "8to10") sort of thing.
RE: RE: RE: Excel Functions, HELLLP, PLZ by M73A on 03-02-2005 at 11:47 PM
quote:
Originally posted by CookieRevised
quote:read my first post in this thread?
Originally posted by may73alliance
Cheers Saralk the formulae actually gives a result but not the right result. lolololol. hmmmm isnt it possible to do =COUNTIF(A1:H1, "8to10") sort of thing.
lol, yea, sorry i got confused with all the info! lol
I typed in the one you said you corrected and got that damn error again. i ajusted saralk's one a bit to =COUNTIF(J1:J98, ">=8")-COUNTIF(J1:J98, ">=10") and that works i think thats right anyway
RE: Excel Functions, HELLLP, PLZ by CookieRevised on 03-02-2005 at 11:51 PM
No, it will NOT return the correct result...
Please read my posts carefully, and notice the colored, bold or underlined parts!!!
quote:notice the red and blue parts
Originally posted by CookieRevised
Like first said by Z_Joanna_ZZZ and corrected by me:
* number of cells in range A1:A55 which have a value between 8 and 10 (thus excl. 8 and 10):
=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">=10")
* number of cells in range A1:A55 which have a value from 8 till 10 (thus incl. 8 and 10):
=COUNTIF(A1:A55;">=8")-COUNTIF(A1:A55;">10")
if the range is:
5.77
6.45
8.00
8.16
9.51
10.00
10.78
the first formula:
=COUNTIF(A1:A55;">8") - COUNTIF(A1:A55;">=10")
=4 - 2
=2
(because only 8.16 and 9.51 are numbers between 8 and 10)
the second formula:
=COUNTIF(A1:A55;">=8") - COUNTIF(A1:A55;">10")
=5 - 1
=4
(because 8.00, 8.16, 9.51 and 10.00 are numbers from 8 till 10, inclusief 10)
your formula:
=COUNTIF(A1:A55;">=8")-COUNTIF(A1:A55;">=10")
=5 - 2
=3 (thus wrong)
=the amount of numbers from 8 (thus incl. 8) to 10 (thus excl. 10)
">" means bigger then. This means that ">8" means bigger then 8... 8 is not bigger then 8 but equal... Thus 8 will not be counted...
">=" means bigger then or equal to. This means that ">=8" means bigger then 8 or equal to 8... Thus 8 will be counted...
RE: Excel Functions, HELLLP, PLZ by jren207 on 03-03-2005 at 12:16 AM
You can use the insert funtion button and use the wizard like screen to enter the range and criteria etc. so that you don't get confused with how to enter it.
Click for better view
RE: Excel Functions, HELLLP, PLZ by M73A on 03-03-2005 at 07:45 AM
woah cheers ppl. thanks a lot for all of the help. still think MS should just make it so you can type between. lol