- Excel help please
possibly a bit of a numpty question:
Let’s say I have a very long column of numbers; random numbers between 1 and 100
And let’s say I want to highlight the numbers which are under, say, 50…. no problem, use conditional formatting or whatever it’s called in the English version of Excel (I have the German version here at work).
But how about if I want to automatically count up how many of the numbers in this column have fulfilled the condition, i.e. without counting them out manually.
Ideally I could do with a figure at the bottom of the column of numbers telling me how many of them have a value less than “x” (won’t always be 50!)
Any ideas ? Any input would be greatly appreciated!Posted 9 years ago
Cheers but I can’t get that to work
Stoner…. do I literally replace your B1 with the boundary value I want, e.g. 50, and it’s only the “less than”/”more than” sign in inverted commas ?
Also, I can’t work out what COUNTIF is in the German version of Excel, grrrr
could be ANZAHL, could be ANZAHL2 … either way it isn’t playing
Thanks for any more input !Posted 9 years agostumpy01Member
I just did this: =COUNTIF(A1:A56,”>50″) in excel and it worked fine. Column A was a load of random numbers I put in. You don’t need &B1 stuff – well not in Excel 2003 anyway!
I you want to make sure all your numbers are numbers…..just do format>cells>number tab and select ‘number’ to however many dp’s you want.Posted 9 years ago
stumpy – the reason for putting in the external criteria reference (e.g. B1) is so that you can vary the criteria when you want to without having to change the code.
and format cells number wont always work, its a common bug. The only surefire way of getting excel to treat it like a number if theres a format problem like that is to use it in a calculation to “clean” it.Posted 9 years agostumpy01Member
Yep, I know why the cell reference was there, but seeing as the guy is having a bit of trouble, I thought it best to start off easy. Then when that works, it’s possible to tinker with it from there but at least you have a working ‘start’ point.
I wasn’t aware of the ‘common bug’ you mention, but most of the Excel stuff I do is with data generated by visioning systems and that always comes out as either numbers or ‘na’ and we filter for ‘na’ before doing anything with it.Posted 9 years ago
The topic ‘Excel help please’ is closed to new replies.