Hi,
I am trying to find a good way to translate text (a word) to a numerical value and then summing if there are multiple words that apply.
I am using excel to record pathology information from fish. For each organ I record any unusual findings, such as hemorrhage, ulcer, etc. For some organs there are more than 1 finding. Each finding can be mild, moderate or severe in severity. I assign a score of 10 for mild, 20 for moderate and 30 for severe. I want to retain the text in the spreadsheet so I can easily see what the finding was and then a have an adjacent column for the total score for that organ. So a basic organization of what I started doing looks like this:
So for each fish there is a total score for each organ and a total score for the entire fish (sum of the scores for all organs). This is currently what was started by a colleague using VBA to create a lookup table that gets activated as an add-in. This is a very clunky approach and often the link to the table breaks resulting in many columns of error messages. We got it to work but I'm hoping there is an easier way. I would prefer to avoid the add-in route. The problem with this route is that the table has to include each variant of finding and severity such as ulcer, ulcer_mod, ulcer_sev.
So can someone suggest a better way of doing this? I included multiple findings in the same cell because it can vary from none to 4 or 5, and with observations from 12 organs, the number of columns in the spreadsheet could be numerous. So I entered all findings from each organ into a single cell to keep things condensed. Maybe this is a bad idea as I can't find information on working with more than 1 piece of info in a single cell. If I split each finding out into a separate cell, the number of columns will grow to an unmanageable number. If someone has a better suggestion I'm all ears (eyes). I could keep it simple and just use the severity score and not include the name of the finding, but this still leaves me with potentially up to 4-5 findings per organ that need to be summed. With a grid of rows that identify the fish and columns that identify organ I am pretty much stuck with needing to enter multiple items in a single cell, unless someone can point out an alternative.
Thanks for any help or suggestions and if I have not made this clear enough let me know.
I am trying to find a good way to translate text (a word) to a numerical value and then summing if there are multiple words that apply.
I am using excel to record pathology information from fish. For each organ I record any unusual findings, such as hemorrhage, ulcer, etc. For some organs there are more than 1 finding. Each finding can be mild, moderate or severe in severity. I assign a score of 10 for mild, 20 for moderate and 30 for severe. I want to retain the text in the spreadsheet so I can easily see what the finding was and then a have an adjacent column for the total score for that organ. So a basic organization of what I started doing looks like this:
| fish number | skin | skin score | fins | fin score | total score |
| 1 | normal | 0 | erode_mod | 20 | 20 |
| 2 | ulcer_mod, hem_sev | 50 | hem_severe | 30 | 80 |
| 3 | ulcer, hem | 20 | erode_mod, hem | 30 | 50 |
So for each fish there is a total score for each organ and a total score for the entire fish (sum of the scores for all organs). This is currently what was started by a colleague using VBA to create a lookup table that gets activated as an add-in. This is a very clunky approach and often the link to the table breaks resulting in many columns of error messages. We got it to work but I'm hoping there is an easier way. I would prefer to avoid the add-in route. The problem with this route is that the table has to include each variant of finding and severity such as ulcer, ulcer_mod, ulcer_sev.
So can someone suggest a better way of doing this? I included multiple findings in the same cell because it can vary from none to 4 or 5, and with observations from 12 organs, the number of columns in the spreadsheet could be numerous. So I entered all findings from each organ into a single cell to keep things condensed. Maybe this is a bad idea as I can't find information on working with more than 1 piece of info in a single cell. If I split each finding out into a separate cell, the number of columns will grow to an unmanageable number. If someone has a better suggestion I'm all ears (eyes). I could keep it simple and just use the severity score and not include the name of the finding, but this still leaves me with potentially up to 4-5 findings per organ that need to be summed. With a grid of rows that identify the fish and columns that identify organ I am pretty much stuck with needing to enter multiple items in a single cell, unless someone can point out an alternative.
Thanks for any help or suggestions and if I have not made this clear enough let me know.
