Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#1 Problem
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#1 Problem
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#1 Problem
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#1 Problem
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#1 Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |