S
Schannah
I'm making a spreadsheet that will help me to test myself on cocktail
recipes. The spreadsheet so far contains a large table with a row of cocktail
names at the top and a list of ingredients down the side. The cell values
represent how many measures of a given ingredient are used in a given
cocktail. So, for example, a Sex on the Beach will have values of 1 entered
into the cells in the rows corresponding to peach schnapps and vodka, and
values of 3 entered into the cells in the rows corresponding to orange juice
and cranberry juice.
What I am now trying to do with this spreadsheet is write some formulae that
will allow me to test my knowledge of the recipes (I am doing this currently
in a much smaller test spreadsheet including only three cocktails). So far I
have used the following formula to generate a cocktail name selected randomly
from the top row:
=INDEX(B11,ROUNDUP(RAND()*COUNTA(B11),0))
This comes up with one of three cocktail names, hopefully each equally
likely to be chosen.
Then, because the result of this formula is constantly changing, I copy out
the first result and use that to refer to the recipe table so that my
spreadsheet can tell me whether I have the ingredients right or wrong.
For instance, I will write out "Sex on the Beach" in cell A19, and in the
rows underneath it I will write out the ingredients that I believe to be
included. To test what happens if I get it wrong, I deliberately omit one
ingredient, put in too much of another, and put in an extra ingredient that
shouldn't even be there.
So:
Sex on the Beach
Vodka
Tequila
Orange juice
Cranberry juice
In the adjacent cells I put the number of measures:
Sex on the Beach
Vodka | 2
Tequila | 1
Orange juice | 3
Cranberry juice | 3
In the cells to the right of the numbers, I put the following formula:
=IF(HLOOKUP($A$29,$A$1:$E$12,VLOOKUP(A30,$A$1:$E$12,5,FALSE),FALSE)=B30,"Right","Wrong")
This returns "Right" if my suggested number of measures for a given
ingredient is the same as that in the table, and "Wrong" if it does not. The
next column over contains the formula:
=IF(C30>0,HLOOKUP($A$29,$A$1:$E$12,VLOOKUP(A30,$A$1:$E$12,5,FALSE),FALSE),"")
This will show the real value whether my guess is correct or incorrect and
#N/A if there is nothing written there at all. This may seem redundant but it
enables me to have an array that only shows the real measures of the
ingredients if I have made a guess at it already.
The problem is that this doesn't show me if I have omitted an ingredient,
which of course in this case I have. In order to fix this I decided to create
a formula in a different cell that would count the number of ingredients I
have proposed that actually appear in the drink [simply
=COUNTIF(D3036,"<>0")] and compare it to the number of cells in the column
headed "Sex on the Beach" or whatever cocktail has been chosen that are not
empty. This last bit is the bit I can't do. Simply, I need to write a formula
that selects a certain column and counts the number of non-zero values in
that column. This is easy enough when you define the column as an array (e.g.
b2:b13) but very difficult when you try to create the definition of this
array using only the generated cocktail name to locate your column. This is
necessary because the cocktail name is variable, and that is the whole point.
[I have tried to hash up a solution by creating ID numbers for the columns
at the bottom of the table, and using
=ADDRESS(2,HLOOKUP(A29,A1:E13,13,FALSE),1,1) and
=ADDRESS(13,HLOOKUP(A29,A1:E13,13,FALSE),1,1) where row 13 contains the ID
numbers, and this returns "$B$2$" and "$B$13" which are the correct limits to
the array I want, but should I enter these two functions into a COUNTA
function, with any combination or lack of quotation marks, I get an error
(except for when I place quotation marks before addressfunction1 and after
addressfunction2, in which case I of course get 1.)]
So: does anybody know how I can get COUNTA to count the non-zero values in
an array that isn't fixed?
Sorry for the length of the question, but I hope the context helped to
describe the problem better.
recipes. The spreadsheet so far contains a large table with a row of cocktail
names at the top and a list of ingredients down the side. The cell values
represent how many measures of a given ingredient are used in a given
cocktail. So, for example, a Sex on the Beach will have values of 1 entered
into the cells in the rows corresponding to peach schnapps and vodka, and
values of 3 entered into the cells in the rows corresponding to orange juice
and cranberry juice.
What I am now trying to do with this spreadsheet is write some formulae that
will allow me to test my knowledge of the recipes (I am doing this currently
in a much smaller test spreadsheet including only three cocktails). So far I
have used the following formula to generate a cocktail name selected randomly
from the top row:
=INDEX(B11,ROUNDUP(RAND()*COUNTA(B11),0))
This comes up with one of three cocktail names, hopefully each equally
likely to be chosen.
Then, because the result of this formula is constantly changing, I copy out
the first result and use that to refer to the recipe table so that my
spreadsheet can tell me whether I have the ingredients right or wrong.
For instance, I will write out "Sex on the Beach" in cell A19, and in the
rows underneath it I will write out the ingredients that I believe to be
included. To test what happens if I get it wrong, I deliberately omit one
ingredient, put in too much of another, and put in an extra ingredient that
shouldn't even be there.
So:
Sex on the Beach
Vodka
Tequila
Orange juice
Cranberry juice
In the adjacent cells I put the number of measures:
Sex on the Beach
Vodka | 2
Tequila | 1
Orange juice | 3
Cranberry juice | 3
In the cells to the right of the numbers, I put the following formula:
=IF(HLOOKUP($A$29,$A$1:$E$12,VLOOKUP(A30,$A$1:$E$12,5,FALSE),FALSE)=B30,"Right","Wrong")
This returns "Right" if my suggested number of measures for a given
ingredient is the same as that in the table, and "Wrong" if it does not. The
next column over contains the formula:
=IF(C30>0,HLOOKUP($A$29,$A$1:$E$12,VLOOKUP(A30,$A$1:$E$12,5,FALSE),FALSE),"")
This will show the real value whether my guess is correct or incorrect and
#N/A if there is nothing written there at all. This may seem redundant but it
enables me to have an array that only shows the real measures of the
ingredients if I have made a guess at it already.
The problem is that this doesn't show me if I have omitted an ingredient,
which of course in this case I have. In order to fix this I decided to create
a formula in a different cell that would count the number of ingredients I
have proposed that actually appear in the drink [simply
=COUNTIF(D3036,"<>0")] and compare it to the number of cells in the column
headed "Sex on the Beach" or whatever cocktail has been chosen that are not
empty. This last bit is the bit I can't do. Simply, I need to write a formula
that selects a certain column and counts the number of non-zero values in
that column. This is easy enough when you define the column as an array (e.g.
b2:b13) but very difficult when you try to create the definition of this
array using only the generated cocktail name to locate your column. This is
necessary because the cocktail name is variable, and that is the whole point.
[I have tried to hash up a solution by creating ID numbers for the columns
at the bottom of the table, and using
=ADDRESS(2,HLOOKUP(A29,A1:E13,13,FALSE),1,1) and
=ADDRESS(13,HLOOKUP(A29,A1:E13,13,FALSE),1,1) where row 13 contains the ID
numbers, and this returns "$B$2$" and "$B$13" which are the correct limits to
the array I want, but should I enter these two functions into a COUNTA
function, with any combination or lack of quotation marks, I get an error
(except for when I place quotation marks before addressfunction1 and after
addressfunction2, in which case I of course get 1.)]
So: does anybody know how I can get COUNTA to count the non-zero values in
an array that isn't fixed?
Sorry for the length of the question, but I hope the context helped to
describe the problem better.