| View previous topic :: View next topic |
| Author |
Message |
JERRYC
General User
Joined: 26 Jun 2011
Posts: 8
|
 Posted: Sun Jun 26, 2011 10:38 am Post subject: USING COUNTIF |
 |
|
ENTER 6 IN B9
ENTER 5 IN B10
ENTER 4 IN B11
ENTER 3 IN B12
FORMULA:
=COUNTIF(B12;"<B9")+COUNTIF(B12;"<B10")+COUNTIF(B12;"<B11")
THE RESULT IS "0" SHOULD NOT IT BE "3" |
|
| Back to top |
|
 |
Ed
Super User
Joined: 28 May 2003
Posts: 1040
|
| Posted: Sun Jun 26, 2011 11:21 am Post subject: |
|
|
Why use COUNTIF with a range of only one cell?
It would more logical to use a simple IF:
| Code: | | =IF(B12<B9;1;0)+IF(B12<B10;1;0)+IF(B12<B11;1;0) |
You could even use the way TRUE is interpreted as 1 and FALSE is interpreted as 0 when added together, and use the formula:
| Code: | | =(B12<B9)+(B12<B10)+(B12<B11) |
|
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Jun 26, 2011 11:33 am Post subject: |
|
|
Countif is a little bit silly in this context.
Anyhow, you can compare with an operator and a value in one string:
=COUNTIF(B12;"<6")+COUNTIF(B12;"<5")+COUNTIF(B12;"<4")
or concatenate an operator string with a reference:
=COUNTIF(B12;"<"&B9)+COUNTIF(B12;"<"&B10)+COUNTIF(B12;"<"&B11)
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
JERRYC
General User
Joined: 26 Jun 2011
Posts: 8
|
| Posted: Sun Jun 26, 2011 11:36 am Post subject: |
|
|
| Being new to the game, I really appreciate your help. Thanks alot |
|
| Back to top |
|
|
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Sun Jun 26, 2011 12:15 pm Post subject: |
|
|
Hi
The Job is done by:
| Code: | | =COUNTIF(B9:B11;">"&B12) |
@villeroy
COUNTIF isn't silly in that Context!
Karolus |
|
| Back to top |
|
|
Ed
Super User
Joined: 28 May 2003
Posts: 1040
|
| Posted: Sun Jun 26, 2011 1:05 pm Post subject: |
|
|
| karolus wrote: | | Code: | | =COUNTIF(B9:B11;">"&B12) |
@villeroy
COUNTIF isn't silly in that Context!
|
Maybe not in that formula, but using COUNTIF as JERRYC did with only a single cell in the range is silly! |
|
| Back to top |
|
|
JERRYC
General User
Joined: 26 Jun 2011
Posts: 8
|
| Posted: Mon Jun 27, 2011 5:56 am Post subject: Assigning a value to what if |
|
|
enter 5 in A1
enter 5 in A2
enter 4 in A3
enter 3 in A4
enter 5 in B1
enter 5 in B2
enter 5 in B3
enter 5 in B4
enter 5 in C1
enter 5 in C2
enter 6 in C3
enter 7 in C4
1. What I want is to find the smallest number in (A1:A4) and assign a 2 in a different cell
2. same as 1, but if the smallest number is more than once assign each one a 1
Do the same for (B1:B4) and (C1:C4)
Last edited by JERRYC on Mon Jun 27, 2011 7:05 am; edited 1 time in total |
|
| Back to top |
|
|
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Mon Jun 27, 2011 6:28 am Post subject: |
|
|
Hi
With Calc-formulas you cannot assign values to *other* cells, you have to do it with Makrocode ( Basic , Python, etc. )
Karolus |
|
| Back to top |
|
|
JERRYC
General User
Joined: 26 Jun 2011
Posts: 8
|
| Posted: Mon Jun 27, 2011 6:42 am Post subject: |
|
|
| karolus wrote: | Hi
With Calc-formulas you cannot assign values to *other* cells, you have to do it with Makrocode ( Basic , Python, etc. )
Karolus |
I would like to assign it to a different cell |
|
| Back to top |
|
|
Ed
Super User
Joined: 28 May 2003
Posts: 1040
|
| Posted: Mon Jun 27, 2011 6:57 am Post subject: Re: Assigning a value to what if |
|
|
| JERRYC wrote: | 1. What I want is to find the smallest number in (A1:A4) and assign it a 2
2. same as 1, but if the smallest number is more than once assign each one a 1
Do the same for (B1:B4) and (C1:C4) |
Formulae can not change the contents of cells. All a formula can do is perform a calculation, and the result of that calculation becomes the value of the cell containing the formula. |
|
| Back to top |
|
|
JERRYC
General User
Joined: 26 Jun 2011
Posts: 8
|
| Posted: Mon Jun 27, 2011 7:06 am Post subject: Re: Assigning a value to what if |
|
|
| Ed wrote: | | JERRYC wrote: | 1. What I want is to find the smallest number in (A1:A4) and assign it a 2
2. same as 1, but if the smallest number is more than once assign each one a 1
Do the same for (B1:B4) and (C1:C4) |
Formulae can not change the contents of cells. All a formula can do is perform a calculation, and the result of that calculation becomes the value of the cell containing the formula. |
I would like it to assign the value in a different cell |
|
| Back to top |
|
|
Ed
Super User
Joined: 28 May 2003
Posts: 1040
|
| Posted: Mon Jun 27, 2011 7:29 am Post subject: Re: Assigning a value to what if |
|
|
| JERRYC wrote: | | Ed wrote: | | JERRYC wrote: | 1. What I want is to find the smallest number in (A1:A4) and assign it a 2
2. same as 1, but if the smallest number is more than once assign each one a 1
Do the same for (B1:B4) and (C1:C4) |
Formulae can not change the contents of cells. All a formula can do is perform a calculation, and the result of that calculation becomes the value of the cell containing the formula. |
I would like it to assign the value in a different cell |
As has already been said, spreadsheet formulae can not assign values to other cells. Repeating that you want to do something impossible does not magically make it possible. |
|
| Back to top |
|
|
JERRYC
General User
Joined: 26 Jun 2011
Posts: 8
|
| Posted: Mon Jun 27, 2011 7:38 am Post subject: Re: Assigning a value to what if |
|
|
| Ed wrote: | | JERRYC wrote: | | Ed wrote: | | JERRYC wrote: | 1. What I want is to find the smallest number in (A1:A4) and assign it a 2
2. same as 1, but if the smallest number is more than once assign each one a 1
Do the same for (B1:B4) and (C1:C4) |
Formulae can not change the contents of cells. All a formula can do is perform a calculation, and the result of that calculation becomes the value of the cell containing the formula. |
I would like it to assign the value in a different cell |
As has already been said, spreadsheet formulae can not assign values to other cells. Repeating that you want to do something impossible does not magically make it possible. |
"Formulae can not change the contents of cells. All a formula can do is perform a calculation, and the result of that calculation becomes the value of the cell containing the formula.[/quote] This is what I want, putting a formula in a different cell to get the result" |
|
| Back to top |
|
|
Ed
Super User
Joined: 28 May 2003
Posts: 1040
|
| Posted: Mon Jun 27, 2011 7:59 am Post subject: |
|
|
| What result are you trying to get? It is really not at all clear what you are trying to do. |
|
| Back to top |
|
|
JERRYC
General User
Joined: 26 Jun 2011
Posts: 8
|
| Posted: Mon Jun 27, 2011 8:02 am Post subject: |
|
|
| Ed wrote: | | What result are you trying to get? It is really not at all clear what you are trying to do. |
Well, if I don't know what I want you surely can't help, thanks any way. |
|
| Back to top |
|
|
|
