Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
Hi there
Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points greater or equals to the number in the set. So for example, with two columns: a 6 b 6 c 4 d 4 e 2 f 1 g 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
Is this what you mean?
=COUNTIF(B1:B7,"="&num_points) -- __________________________________ HTH Bob "Francois" wrote in message ... Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points greater or equals to the number in the set. So for example, with two columns: a 6 b 6 c 4 d 4 e 2 f 1 g 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
On Nov 19, 9:43*am, "Bob Phillips" wrote:
Is this what you mean? =COUNTIF(B1:B7,"="&num_points) -- __________________________________ HTH Bob "Francois" wrote in message ... Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points *greater or equals to the number in the set. So for example, with two columns: a * 6 b * 6 c * 4 d * 4 e * 2 f * *1 g * 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois Yes, but num_points is variable since it's the number of items in the set (or the row position if the items are ordered) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
So how do you work out the value of num_points?
-- __________________________________ HTH Bob wrote in message ... On Nov 19, 9:43 am, "Bob Phillips" wrote: Is this what you mean? =COUNTIF(B1:B7,"="&num_points) -- __________________________________ HTH Bob "Francois" wrote in message ... Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points greater or equals to the number in the set. So for example, with two columns: a 6 b 6 c 4 d 4 e 2 f 1 g 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois Yes, but num_points is variable since it's the number of items in the set (or the row position if the items are ordered) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
On Nov 19, 11:20*am, "Bob Phillips" wrote:
So how do you work out the value of num_points? -- __________________________________ HTH Bob wrote in message ... On Nov 19, 9:43 am, "Bob Phillips" wrote: Is this what you mean? =COUNTIF(B1:B7,"="&num_points) -- __________________________________ HTH Bob "Francois" wrote in message .... Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points greater or equals to the number in the set. So for example, with two columns: a 6 b 6 c 4 d 4 e 2 f 1 g 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois Yes, but num_points is variable since it's the number of items in the set (or the row position if the items are ordered) If I do this "by hand", I count the lines and stop when the value of points is lower then the row I am in (which is also the number of items in my set). a 6 1 b 6 2 c 4 3 d 4 4 e 2 I stop above since the 5 rows has a value of 2 points f 1 g 0 I there a way to do this with a formula ? (I hope I'm clearer ?) Francois |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
How about this array formula
=MAX(IF(B1:B7=ROW(B1:B7),ROW(B1:B7))) -- __________________________________ HTH Bob wrote in message ... On Nov 19, 11:20 am, "Bob Phillips" wrote: So how do you work out the value of num_points? -- __________________________________ HTH Bob wrote in message ... On Nov 19, 9:43 am, "Bob Phillips" wrote: Is this what you mean? =COUNTIF(B1:B7,"="&num_points) -- __________________________________ HTH Bob "Francois" wrote in message ... Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points greater or equals to the number in the set. So for example, with two columns: a 6 b 6 c 4 d 4 e 2 f 1 g 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois Yes, but num_points is variable since it's the number of items in the set (or the row position if the items are ordered) If I do this "by hand", I count the lines and stop when the value of points is lower then the row I am in (which is also the number of items in my set). a 6 1 b 6 2 c 4 3 d 4 4 e 2 I stop above since the 5 rows has a value of 2 points f 1 g 0 I there a way to do this with a formula ? (I hope I'm clearer ?) Francois |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
On Nov 19, 1:10*pm, "Bob Phillips" wrote:
How about this array formula =MAX(IF(B1:B7=ROW(B1:B7),ROW(B1:B7))) -- __________________________________ HTH Bob wrote in message ... On Nov 19, 11:20 am, "Bob Phillips" wrote: So how do you work out the value of num_points? -- __________________________________ HTH Bob wrote in message ... On Nov 19, 9:43 am, "Bob Phillips" wrote: Is this what you mean? =COUNTIF(B1:B7,"="&num_points) -- __________________________________ HTH Bob "Francois" wrote in message .... Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points greater or equals to the number in the set. So for example, with two columns: a 6 b 6 c 4 d 4 e 2 f 1 g 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois Yes, but num_points is variable since it's the number of items in the set (or the row position if the items are ordered) If I do this "by hand", I count the lines and stop when the value of points is lower then the row I am in (which is also the number of items in my set). a * 6 * 1 b * 6 * 2 c * 4 * 3 d * 4 * 4 e * 2 * *I stop above since the 5 rows has a value of 2 points f * *1 g * 0 I there a way to do this with a formula ? (I hope I'm clearer ?) Francois That's great ! Thanks it works I was on the same array formula, a little more verbose =MAX(ROW(a6c)*IF (a6c=ROW(a6c);1;0)) Where a6c is the named range of points values. Now, both solution works only if the points are in decreasing order. I try to include the following array formula to have the same result with unordered values =LARGE(a6c;ROW(a6c)) but for the moment I have error messages only... Francois |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
On Nov 19, 2:08*pm, wrote:
On Nov 19, 1:10*pm, "Bob Phillips" wrote: How about this array formula =MAX(IF(B1:B7=ROW(B1:B7),ROW(B1:B7))) -- __________________________________ HTH Bob wrote in message .... On Nov 19, 11:20 am, "Bob Phillips" wrote: So how do you work out the value of num_points? -- __________________________________ HTH Bob wrote in message .... On Nov 19, 9:43 am, "Bob Phillips" wrote: Is this what you mean? =COUNTIF(B1:B7,"="&num_points) -- __________________________________ HTH Bob "Francois" wrote in message ... Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points greater or equals to the number in the set. So for example, with two columns: a 6 b 6 c 4 d 4 e 2 f 1 g 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois Yes, but num_points is variable since it's the number of items in the set (or the row position if the items are ordered) If I do this "by hand", I count the lines and stop when the value of points is lower then the row I am in (which is also the number of items in my set). a * 6 * 1 b * 6 * 2 c * 4 * 3 d * 4 * 4 e * 2 * *I stop above since the 5 rows has a value of 2 points f * *1 g * 0 I there a way to do this with a formula ? (I hope I'm clearer ?) Francois That's great ! Thanks it works I was on the same array formula, a little more verbose =MAX(ROW(a6c)*IF (a6c=ROW(a6c);1;0)) Where a6c is the named range of points values. Now, both solution works only if the points are in decreasing order. I try to include the following array formula to *have the same result with unordered values =LARGE(a6c;ROW(a6c)) but for the moment I have error messages only... Francois OK Here we are =MAX(IF(LARGE(a6c;ROW(a6c))=ROW(a6c);ROW(a6c))) as an array formula work even if the data in a6c are not ordered. Many thanks for your help !! Francois |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
=IF(ROW(A2)-ROW($A$2)+1<=B2,A2,"")
Is this what you want? You say the input table is sorted descending. If you copy the formula down it will start giving blanks when the relative position in the data becomes greater than the points. HTH Kostis Vezerides On Nov 19, 10:32*am, Francois wrote: Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points *greater or equals to the number in the set. So for example, with two columns: a * 6 b * 6 c * 4 d * 4 e * 2 f * *1 g * 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of items in a set
On Nov 19, 4:40*pm, vezerid wrote:
=IF(ROW(A2)-ROW($A$2)+1<=B2,A2,"") Is this what you want? You say the input table is sorted descending. If you copy the formula down it will start giving blanks when the relative position in the data becomes greater than the points. HTH Kostis Vezerides On Nov 19, 10:32*am, Francois wrote: Hi there Is there a formula to get the number of items in a list defined like this:, product a, b, c ... has points, and the list is ordered with decreasing points values. The set is composed of the products that have points *greater or equals to the number in the set. So for example, with two columns: a * 6 b * 6 c * 4 d * 4 e * 2 f * *1 g * 0 The set is made of a, b, c, d and the result would be 4. I know I could solve this with vb, but I wonder if a formula could give me the result. I have tried something with COUNTIF be the trick is that I would need a criteria saying value in current cell <= row of the current cell Thanks a lot for any help Francois The above Bob's array formula works if the values are ordered =MAX(IF(B1:B7=ROW(B1:B7),ROW(B1:B7))) This array formula works also for unordered data =MAX(IF(LARGE(B1:B7;ROW(B1:B7))=ROW(B1:B7);ROW(B1 :B7))) Francois |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sum a variable number of items | Excel Worksheet Functions | |||
Sum a different number of items | Excel Worksheet Functions | |||
number of items fit into item | Excel Worksheet Functions | |||
Number of items in a colum | Excel Worksheet Functions | |||
Number of items per day in chart. | Charts and Charting in Excel |