#1 Problem

H

hos

I apologize if this winds up being stupid-simple, but I have not used
Excel in quite some time and could not find an answer in tutorials or
on the web...

Is there a way to put a number in a cell, but have Excel calculate it
as a different quantity?

We are tabulating rankings of product: People chose their ten favorite
items and then were told them to rank them 1(highest)-10 (lowest).
We're assigning quantities to their rankings: #1=15, #2=14, etc. We
want to have their original rankings visible, but have the spreadsheet
calculate based on the quantities. Is there a way to enter a 1 and
have it calculate it as 15?

Thanks for whatever help you can offer,

hs
 
M

mcrae

I just fiddled around and heres what i came up with, all you do is use
an if function and make the return_if_false statement a new if
statement

=IF(C17=1, 15, (IF(C17=2, 14, (IF(C17=3, 13, C17)))))

this is only a simple example assuming 1=14, 2=14, 3=13, and if a
nubmer other than 1, two, or 3 are given, then it will return that same
number

put this formula in a cell you aren't using, and then just refer to
that cell instead of the one where people input their rankings
 
R

Ron Coderre

You'll probably get some good guesses at what you want, but I think
if you tell us how you will be using the re-assigned values...you'll
get
exactly what you want. Will you be adding them up? Multiplying them by
some other value? Relating them to other factors?

Regards,
Ron
 
R

Ragdyer

Say your rankings starting in B1, and continuing down the column, try this
in C1, and copy down as needed:

=CHOOSE(B1,15,14,13,12,11,10,9,8,7,6)

Then you can simply total the column:

=Sum(C:C)
 
H

hos

Ron said:
You'll probably get some good guesses at what you want, but I think
if you tell us how you will be using the re-assigned values...you'l
get
exactly what you want. Will you be adding them up? Multiplying them b
some other value? Relating them to other factors?
Click to expand...

This is the basic set-up: Each row has a product name, each column
user name; there will be more than 100 of each. The re-assigned value
will be simply added to a "total score," and data will be sorte
according to that value. Another column is simply a COUNT column t
note how many uses rated that product. A third column (optional, but
thought it interesting) would be an "Average ranking" based on th
original 1-10 rating.

Thanks much for the responses so far
 
P

Pete

How about:

=16 - B1,

where B1 is the ranking?

If you want to guard against spurious entries in B1, you can have:

=IF(AND(B1>0,B1<=10,B1=INT(B1)), 16-B1, "Invalid ranking")

Pete
 
Top