Getting desperate...need direction!
Posted by Darcy Guineay on January 20, 2002 9:29 AM
I wrote earlier this week about a problem I have and I thank Juan Pablo G.for writing back, but it still was not the answer I was looking for.
Here is the problem:
I am making schedules using different symbols in different cells. When I print out the spreadsheet it has to contain the symbols, however I need the symbols to equate to numeric values.
Example:
----- = 1 and --X-- = .5
A1 B1 C1 D1 E1 F1
----- ----- --X-- ----- =sum(A1:D1)
The answer to F1 is 3.5
When it prints out it will look like this
----------X------- 3.5
Does anyone have any idea how this can be done or at least point me in the right direction?
Darcy from Honolulu, Hawaii USA
Posted by Tom Dickinson on January 20, 2002 9:56 AM
Don't know what you got earlier, but it looks like this to me:
=IF(A1="-----",1,If(A1="--X--",0.5,0)) + etc for B1, C1...
You can only nest 7 if statements at once, so if you have more than 7 symbols, you will need to break it up
E.g.
=If (A1="-----",1,0)+if(A1="--X--",0.5,0)+...
Posted by Aladin Akyurek on January 20, 2002 12:25 PM
Darcy --
Given the possible reading of your first post, the propsal Juan made allows for an extended set of numeric operations.
The current post is more specific and Tom has proposed a practical solution, which is however bounded by the number of symbols that you might have (embedding the danger of a long chain of IFs).
What follows is a different proposal.
Make a 2-column list of your symbols and the values associated with them in a worksheet named Admin, say, in columns B and C from row 2 on.
Lets say that B2:C4 houses the following symbols with their associated values, which can be expanded at will (Note: I use _ insted of -, because the former is easier to enter in a cell.).
{"_____",1;
"__X__",0.5;
"____X",2}
Select the cells with symbols in the first column and name the selection SYMS via the Name Box on the Formula Bar.
Select all cells of this 2-column list and name the selection ALIST via the Name Box on the Formula Bar.
How To Use All This?
In a different worksheet in A1:A4 enter one by one:
{"_____";
"__X__";
"__X__";
"_____"}
To sum A1:A4, you have to array-enter the following formula in some cell:
=SUM(N(OFFSET(ALIST,MATCH(A1:A4,SYMS,0)-1,1,1,1)))
In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.
Another example. In A1:E1 enter the following symbols:
{"_____","_____","__X__","__X__","____X"}
In some cell again array-enter:
=SUM(N(OFFSET(ALIST,MATCH(A1:E1,SYMS,0)-1,1,1,1)))
Enjoy.
Aladin
===========
