asign a number to a text string

G

gimme_donuts

it has to be very easy but I have not found the way to do it.

What I'm tryig to do is some list of companies and for their services I want
to use Yes for 1, No for 0, Sometimes for 0.5 and Maybe for 0.25.

just asigning these numbers to the text is what I want, so that it is easely
readable and editable for everyone and it still can make some addition of it
in the end.

Example:

Kwik e-mart > Fruits - Vegetables - Meat - Fish - Fresh Total
score
Yes Yes Sometimes No Maybe
2.75


To add this data, I used the validation function so that there is a dropdown
menu for each criteria (Yes, No, Sometimes, Maybe)
 
M

Max

One play to deliver it ..

Assume the col headers:
Fruits, Vegetables, Meat, Fish, Fresh
are in B1:F1, and the DVs are in B2:F2 down

a. Set up this reference table in I2:J4

Yes 1
No 0
Sometimes 0.5
Maybe 0.25

b. Then place in G2:
=IF(COUNTA(B2:F2)<5,"",SUMPRODUCT(N(OFFSET($J$1,MATCH(B2:F2,$I$2:$I$5,0),))))
Copy G2 down as far as required

As per your post, if B2:F2 contains the 5 DV selections:
Yes Yes Sometimes No Maybe
Click to expand...
then G2 will return the required: 2.75
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
 
M

muddan madhu

bit long .....

assumed B2:E2 has yes,no,sometimes,maybe.....

in F2 put this formula ( use ctrl + shift + enter )

=SUMPRODUCT(IF(B2:E2="yes",1,IF(B2:E2="no",0,IF(B2:E2="sometimes",
0.5,IF(B2:E2="Maybe",0.25)))))
 
J

Joerg Mochikun

Here is a simplified example, using the power of range names:

Put following texts into A10:A14: Select,Yes,No,Sometimes,Maybe
Now put next to it into B10:B14 the corresponding values: 0,1,0,0.25,0.5

Now select A10:B14 and go to menu Insert>Name>Create>check "Left column" >OK
You've just created range names for B10 to B14.

Create a cell validation for cell B1 by using the list in A10:A14 as
criteria.
Choose "Select" from the drop-down list in B1 and copy the cell through D1.

You now should have values "Select" in B1:D1.

Now here comes the meat:
Put following formula into A1:
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1)

This will result in the sum of B1:D1, based on the text is these cells.
Since the formula will result in an error message if any of the cells is
blank, I added the "Select" value as a default (if you want to be nice you
can use "Please_select"). Of course you could also trap the error with an
additional IF condition, but I wanted to keep the formula simple.

Cheers,

Joerg Mochikun
 
G

gimme_donuts

Thank you this is just what I needed!! It works great and it is very easy to
configure!


I tried everybody else's suggestions but I had problems with them. For the
sake of it I'll quickly say what didn't work:

Max : somehow, when I copy the cells it doesn't display any result even
though it changes the ranges and I input information in them. Furthermore I
couldn't reproduce it in another place in the worksheet (of course I changed
the ranges...even though I didn't really understand the $J$1 position in your
example and the $I$2:$I$5,0....

muddan madhu : your version seemed nice but I somehow coudn't make it work
(even though I just quickly tried it in some other place on the sheet...)
 
G

gimme_donuts

hey is there a way to simplify the formula
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1) ????

what I mean is that now, I have added more than 200 different objects with
each of them having 7 cells/options that have a validation and which are then
summed with the above formula.

BUT now I want to make the sum of each of the cells/options. So the formula
would look like "INDIRECT(B1)+INDIRECT(B2)" until "+INDIRECT(B200)" which
would be very long...
so my question is : is there a way to make it shorter and easier to handle???
 
Top