Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Count occurence with user defined upper&lower limit.

I would like to know how to count occurence in a column/row of data with
user define-able of its upper & lower limit in a table format.

Detail as illustrated below,
series data:
1,1,2,2,3,3,4,4,5,5

lower limit upper Limit occurence
2 (user key in) 4 (user key in) (*need formula here to get
"4")

It was unachievable by using COUNTIF command since i can't assign a cell
value in to COUTIF(A1:J1,"=A5") - COUNTIF(A1:J1,"B5") as e.g

Thanks and hope hear from yours reply soon.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Count occurence with user defined upper&lower limit.

All you need to do is enter =COUNTIF(A1:A10,B1) for your lower bound and
=COUNTIF(A1:A10),B2) for your upper bound, assuming that the range is in
A1:A10 and your lower bound cell and upper bound cell are in B1 and B2
respectively.

"Chan" wrote:

I would like to know how to count occurence in a column/row of data with
user define-able of its upper & lower limit in a table format.

Detail as illustrated below,
series data:
1,1,2,2,3,3,4,4,5,5

lower limit upper Limit occurence
2 (user key in) 4 (user key in) (*need formula here to get
"4")

It was unachievable by using COUNTIF command since i can't assign a cell
value in to COUTIF(A1:J1,"=A5") - COUNTIF(A1:J1,"B5") as e.g

Thanks and hope hear from yours reply soon.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 266
Default Count occurence with user defined upper&lower limit.

"Chan" skrev i en meddelelse
...
I would like to know how to count occurence in a column/row of data with
user define-able of its upper & lower limit in a table format.

Detail as illustrated below,
series data:
1,1,2,2,3,3,4,4,5,5

lower limit upper Limit occurence
2 (user key in) 4 (user key in) (*need formula here to get
"4")

It was unachievable by using COUNTIF command since i can't assign a cell
value in to COUTIF(A1:J1,"=A5") - COUNTIF(A1:J1,"B5") as e.g

Thanks and hope hear from yours reply soon.



Chan

The proper syntax is:

COUNTIF(A1:J1,"="&A5) - COUNTIF(A1:J1,""&B5)

The string "=A5" Excel "sees" as 4 separate characters, not as a cell
reference.


--
Best regards
Leo Heuser

Followup to newsgroup only please.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default Count occurence with user defined upper&lower limit.

Chan wrote:
I would like to know how to count occurence in a column/row of data with
user define-able of its upper & lower limit in a table format.

Detail as illustrated below,
series data:
1,1,2,2,3,3,4,4,5,5

lower limit upper Limit occurence
2 (user key in) 4 (user key in) (*need formula here to get
"4")

It was unachievable by using COUNTIF command since i can't assign a cell
value in to COUTIF(A1:J1,"=A5") - COUNTIF(A1:J1,"B5") as e.g

Thanks and hope hear from yours reply soon.



Just add an ampersand

=COUNTIF(A1:J1,"="&A5)- and so on

Regards,

Peo Sjoblom
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Count occurence with user defined upper&lower limit.

Dear Dave,

I think i din't get my question asked nicely ...

Lets start again.

I have a series of data:
1,1,1,2,2,2,3,3,4,4

Under normal circumstances, if i want to group the data as table below, it
would like this:

Value range Occurence
1-2 =COUNTIF(A1:A10,"=1") -
COUNTIF(A1:A10."2")
2-3 =COUNTIF(A1:A10,"=2") -
COUNTIF(A1:A10."3")

And what if i want a table as per below which allow user to key in their
lower & upperlimit and i can get the occurence ultimatly as above

Value Range Occurence
lower Upper
1(user key in) 2(user key in) =(*need formula here*)
2(user key in) 3(user key in) =(*need formula here*)


Hope i get it clear.
Hope to hear from your favorable response soon.
Thanks!

-Chan

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Assigining User Defined Functions to Categories in Addins Jim Excel Worksheet Functions 0 June 27th 06 12:09 PM
problem with user defined function panjo Excel Discussion (Misc queries) 3 June 16th 06 11:18 AM
User defined functions without using VBA. [email protected] Excel Worksheet Functions 0 June 13th 06 05:49 PM
how to count occurence of numbers separated by , in a single cell kish20 Excel Worksheet Functions 3 June 10th 05 07:49 AM
User defined charts- font size too small Bill B Charts and Charting in Excel 1 December 30th 04 07:23 PM


All times are GMT +1. The time now is 10:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"