#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

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

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Originally Posted by 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?

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.

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