#1 Problem

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


--
hos
------------------------------------------------------------------------
hos's Profile: http://www.excelforum.com/member.php...o&userid=30253
View this thread: http://www.excelforum.com/showthread...hreadid=499242

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


--
mcrae
------------------------------------------------------------------------
mcrae's Profile: http://www.excelforum.com/member.php...o&userid=30252
View this thread: http://www.excelforum.com/showthread...hreadid=499242

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=499242

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)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"hos" wrote in message
...

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


--
hos
------------------------------------------------------------------------
hos's Profile:
http://www.excelforum.com/member.php...o&userid=30253
View this thread: http://www.excelforum.com/showthread...hreadid=499242

Ron Coderre Wrote:
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?

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

Thanks much for the responses so far.


--
hos
------------------------------------------------------------------------
hos's Profile: http://www.excelforum.com/member.php...o&userid=30253
View this thread: http://www.excelforum.com/showthread...hreadid=499242

How about:

=16 - B1,

where B1 is the ranking?

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

=IF(AND(B10,B1=10,B1=INT(B1)), 16-B1, "Invalid ranking")

Pete