Can this be done in Excel please?

[fwells11]

I want to paste in a list of stock symbols and then use my own rating
system to rank them. I have a list of criteria I will use to rank them
with but I want to map written values to numeric values and use those
to create a score for each stock.

Ill try to explain in a little more detail. Below is a very simplified
version of what I am trying to achieve.

Column A | ColumnB | ColumnC | ColumnD | Score |
SYMBOL | Trend | Close Price| Volume |

ColumnB/C/D all use drop down lists with friendly names to pick from.
For example, ColumnB would have the choices Up, Down and Sideways as
the three options in the dropdown list. Each choice needs to map to a
numeric value. Let's say Up has a value of 1, Down has a value of -1
and Sideways has a value of 0.

Similarly, the other columns would be setup in the same manner.

Finally I want the Score cell to tally the values of columns B,C and D
etc.

I can get dropdown lists to make my friendly choices but I cannot get
any further. I tried using VLOOKUP because all the research I could
find made it seem like that was the correct method, but my Excel
ineptness becomes apparent in trying to make it work.

I would much appreciate some help please.

Oh, and to further complicate this a little, I would also like to make
each list configurable without having to recode a bunch of stuff. My
current scoring strategy is still very infantile and I expect to be
adjusting it many times before I settle on a scoring system I feel is
accurate. For this reason I would like to have each list on its own
worksheet rather than multiple ones on a single worksheet. This would
make it more intuitive to update for me...

 

[camjohnson]

One thing that I think would work is an embedded "IF" statement. For
example IF(B2="up", E2=E2+1, IF(B2="down", E2=E2-1,
IF(B2="sideways",E2=E2, E2="error"))). Naturally you could keep going
with more options and changing the options is also possible. Just make
sure you reset the score to zero before starting.
My personal preference is to just use more columns. Meaning have a
column F that is IF (B2="up", F2=1, IF(B2="down", F2=-1,
IF(B2="sideways", F2=0))). They do a similar column G for the
conversion of column C, and column H for the conversion of column D.
Then you can say your column "Score" has the formula =F2+G2+H2. That
way takes a few more columns but it is cleaner, easier to interpret,
and doesn't have a problem with having to reset to zero.
Naturally you can fill down so you don't have to rewrite all the
formulas.

 

[fwells11]

Thank you for your feedback. Your method will dramatically increase
the amount of cells I need per symbol and one of my primary goals is to
keep the main page as clean as I can. By this I mean only having one
row per stock symbol and keeping the rows short enough to avoid any
sideways scrolling.

By itself this constraint would make it impossible to fit all the
necessary columns I need onto one row, but is it possible to have
another worksheet used to hold all the temporary/working data?

What I mean by this is having each dropdown choice add its mapped value
to a cell in another worksheet. The score could then be calculated by
tally the columns in that 'Working' worksheet and returning the
cumulative score to the Score cell in the Main sheet. If this is
feasible, can this all be done without switching worksheets too?

TIA

 

[BruceP]

I have found that situations like this are most easily handled (for me)
by using a StdData worksheet to do your basic work, behind the scenes.
I also tend to use named cells (or ranges) to hold "constant" values
(that may change when I improve my workbook). Your "Up", "Down" and
"Sideways" values would be prime candidates for this. You code them
once, then refer to the names, instead of the actual values, throughout
your workbook. If you decide later to change the values for those,
you'd only have to change them in one place.

You can easily hide your StdData sheet, so only your "clean"
presentation sheet is visible.

 

[fwells11]

Thanks for your reply Bruce. Forgive my ignorance but I have no idea
what a StdData worksheet is and searching in the Excel help for it
results no results either. From your description it sounds like it may
well be what I am looking for. Can you elaborate a little or perhaps
email me a example please?

 

[camjohnson]

I think he is just referring to a worksheet that you are not going to
print out.... more of a scratch pad. On one file I'm working on I have
7 worksheets that have different outputs that users might want to see.
I also have 5 worksheets that the end user doesn't see (can be hidden
if necessary). Those are ones that have intermediate data in columns
that make it easy for me to program and understand (makes it logical).
Then the final data is placed in the nice format for the end user. You
can actually 'hide' the extra sheets but I just change the order so
they are at the end and move the display divider at the bottom of the
page to cover them (most people don't even know they are there). Here
is one reference from a worksheet named "playlist" that uses cells from
another sheet called "Reference". This actual formula doesn't do much
but it shows how to refer to cells from other worksheets.

=IF(Reference!C2="", "ZZ", IF(Reference!C2=" ", "ZZ", Reference!C2)).

 

[Guest]

you can just hide your "helper" columns or have them over to one side out of
normal view