Formulas

[Marie]

I have created a table that looks something like this:
Column A = Class Number
Column B = Option Number
Column C = Number of Voters
Column D = Amount Total of Class-Option

So, I need to do the following:

Given that Class Number = 1, I need to calculate the
number of Voters who have chosen Option 1 under Class 1,
number of Voters who have chosen Option 2 under Class 2,
and so on from a database range.

Then, I need to find the total $$$ amounts for each
Option under each Class based on the number of voters for
each.

For example, Class Number = 1, Option Number = 1, and it
found a number of (3) voters in this specific category.
Now, I need to get the total $$$ amounts for the total
number of voters under Option 1, Class 1.

Can anyone help me to find the appropriate function to
use for this spreadsheet???

Thanks!!

Marie

 

[Steve Smallman]

Marie,

If I understand you correctly, each row contains a unique
identifier located in a combination of columns a & b. And
a corresponding number (column c) which needs to be
multiplied by a factor to produce a result in column d.

If so,

your problem appears to be determining the factor. The
immediate answer I see for you is a slightly complex use
of the VLOOKUP function. The other alternative I would
use is to create a user defined function, but that is a
little more complex still.

what I think you need to do is create a lookup table on
another sheet. Let's call that sheet "lookup". you then
need to place the factors in a table with the identifier
in the first column. Here is where it gets tricky...
Vlookup only looks up one identifier, so in the lookup
table we need to combine both identifiers. So what we can
do is use a format such as ClassNumber_OptionNumber.

So class 1 option 1 is entered 1_1, class 3 option 4 as
3_4. Then place the relevant factors in the next column.

So our table could look like

A B
1 1_1 2
2 1_2 5
3 2_1 3

and so on.

Now back to your sheet with the data
Think of vlookup as Find what, where, give the data from
which column, and is close enough good enough.

Excel help calls it
Lookup_Value (what)
Table_Array (where)
Col_index_num (which column)
Range_lookup (is close enough good enough)

so your data is in cols A to D and we assume that row 1
is column headings and data begins at row 2.

in cell D2, enter the formula
"=vlookup(a2&"_"&b2, lookup!$A$1:$b$50, 2, false)"

assuming your lookup table falls within the range A1:B50,
if not adjust the range. use absolute references for the
table ($a$1:$b$50) so that it stays the same when you
copy the formula.

The tricky bit is the first parameter where we combine
the values in columns a & b to make it look like the
values in the first column of the lookup table.

good luck

Steve