Is there anyone here who can show me how to set up some formulas on a spreadsheet using either Excell for Windows or Numbers for iOS? What I am attempting sounds fairly easy in my head but I cannot figure out how to get it into the program.
What I want is a two row per unit chart. To make it easy to describe lets say A1/A2 is the unit, B1 & 2 would be values for the unit (alphabetic or words) and the final column translates all of the column values from a letters to a number and adds them together. There would actually be more columns of values but I think this covers basically what I had in mind.
The translation from alphabet to number would be covered on a seperate page where I can play with the exact amount each value is worth. Something like an "A" would be worth 8 points and having two "A's" would add up to 16. As a final note: different columns are also given higher values so an "A" in colum B might be worth 8 but an "A" in column C might be worth 8 times 1.5 (12 total).
Is this possible? I had a rough idea like this I did already where all the values where actually writen out (8's instead of A's for example) but wanted something simpler to edit, I had to go back and change some of the values which required me to change nearly every cell.

Richard

So, if I understand what you're saying, you want a setup where you have, say, AABC and ABCCD (or whatever combination of letters you come up with), correct?

In that case, there are a couple of steps to go through. You'll need to parse the initial string into its component letters (so AABC would be A A B C across 4 different cells). Once you have it parsed out, you can use either If/Else If/Else statements to translate over to their respective values, add those up, and then return the result by the entry, or a series of CountIf() statements to tally the number of each character in a given entry and then multiply them by their value (which generally looks like =countif(condition1,range of parsed characters).

There are a few functions that you can use to shortcut the process, such as (IIRC, don't have the program up in front of me) Char(), which returns the numeric value of the character according to ASCII, but those wouldn't allow you to tweak the value of each individual stat.

Of course, if you're just looking for a single rating (Fair, Bad, Good, etc), then you can just run an If/Else If/Else function to separate them out (which works out in a rather interesting fashion in most spreadsheets as =if(condition1, value of condition1, if(condition2, value of condition2, if(...))) - and can get quite messy if you're looking at more than

Yeah, I've done stuff like this a time or two...

Both answers seemed to wrok for what was given but neither are quite what I was thinking about. Maybe I'm not punching it in correctly but maybe I just don't know how to describe it fully.

I have 100 or so idividual units of information I am comparing. A single unit takes up 10 cells from C5 to H6 (two rows, five columns). Each individual unit has 10 scores (one per cell). Scores in the 5th row are ranked 1 to 4 and worth from 1 to 8 points. Scores in the 6th row are ranked F to A and worth from 1 to 16 points. In cell L6 I wanted to have a grand total for all 10 scores but not all scores are given equal weight, for example the C5 might be worth 100% base value while C6 is worth 200% and D5 might be worth 150%. The cells from I5 to M6 are used but not part of the math (not scores).

Do I just need to change the format so that it is all a single column for each individual unit with 100 columns? The end result was to be something that was easy for me to read at a glance but I am leaning towards doing it by hand again. I already have it completed that way I just wanted to see how the totals would change if I modified what a given score was worth (say changing an A from 16 to 32 points) or if I gave more importance to one cell and less to another (such as changing C5 to 200% and C6 to 150%). Also thought it could allow me to add a new potential rank for each row (such as adding an "E" or "5" level to the possible scores).

I would have seen something like:
1-3-2-1-3
F-B-B-F-C

These traslate into values of:
1-4-2-1-4
1-8-8-1-4

Then on the far right I would see a total of:
34 (Cell L5 is just the sum of the translated values)
46 (Cell L6 with weighted values; arbitrary numbers for this example)

Richard

Think I figured it out now. Used Zenguy's method and made it into 20 cells instead of 10 per unit. Column 1 is my input, column 2 is the translation, repeat four times. Then hide all the translations. Took me WAY too long to figure out how to do that but it's done now. And I can add in additional bits of information as well (I think). Thanks guys.

Richard

Great. Glad you got it sorted, Richard.

p.s. discovered that the FALSE tag on VLOOKUP works with unsorted tables, so you don't even ahve to get your translation table in alphabetical order.

I absolutely love VLOOKUP. I've done so much stuff in Excel using that function.

I even built a huge spreadsheet that takes my character's base stats and generates collectible-card-sized cards of the creatures that I can polymorph into in Pathfinder. I had to type in stats for 350+ creatures into a table and then use VLOOKUP like mad to do all the calculations.