How to apply a list of values to

Question

Level 1

0 points

How to apply a list of values to

I have a list of medical procedures, with each one being assigned a certain value (weighting).

I need to apply these values to a data set . The best way to describe this is to give an example.

Here is the data set:

Doctor Name Procedure Performed
-------------------------------------------------------
Smith chest xray
CT head
MRI foot

chest xray

Jones ultrasound neck
chest CT

chest xray

The values are:
chest xray=1
CT Head = 2.4
MRI foot=5.6
ultrasound neck = 1.5
chest CT= 3.1
etc.

So the final result would be able to compare Dr. Smith with Dr. Jones after applying the values above (weighting factors) to the corresponding data (xray, etc.). So the resulting column would assign "2.4" to every place in the sheet where it said "head CT", etc.

I need a spreadsheet where I can just add the procedures performed to a column, and the spreadsheet automatically applies the weighting values and sums the result for each individual doctor. I don't know how to do this in excel or numbers.

Can anyone help?

Posted on Apr 17, 2014 5:54 AM

Reply

Answer 1

Best reply

Jerrold Green1

Level 7

30,210 points

Apr 17, 2014 6:44 AM in response to bradyfans

BradyFan,

Add a table and name it Lookup Weight.

in your main table, add a column after Procedure Performed in which to place the expression that fetches the weight for the procedure.

In the Weight column of the main table, you would write:

=LOOKUP(B,Lookup Weight::A, Lookup Weight::B)

Fill down a reasonable number of rows. Then shorten the table to that number of rows. For any row that has no procedure data, Lookup will return a not found error, so empty rows are a visual problem Fitting the table exactly to the data solves this. You would click on the Add Row handle to expand the table for each new entry. If you don't like that way of suppressing the error and don't want to see the error, then you can alter the expression to test for the presence of data.

The revised expression would be:

=IF(LEN(B)>0, LOOKUP(B,Lookup Weight::A, Lookup Weight::B), ".")

That expression will leave a little dot where the weight will go when a procedure is added.

Jerry

Reply

Answer 2

bradyfans Author

Level 1

0 points

Apr 17, 2014 7:40 PM in response to Jerrold Green1

Thanks Jerry.

I tried your methodand it partially worked for me, but it won't apply the weighting factors all the way down the column. It seems like the boxes on the right (the weighting factors) want to move down with the data set on the left, if that makes any sense. So I have to manually apply the formula to different cells in column c, instead of it automatically calculating values for the whole column. I have included a screen shot. Any ideas?

User uploaded file

Reply

Answer 3

Apr 18, 2014 5:55 AM in response to bradyfans

Sorry, I'm not seeing your screen shot.

Jerry

Reply

Answer 4

bradyfans Author

Level 1

0 points

Apr 18, 2014 5:13 PM in response to Jerrold Green1

Sorry, I cut and pasted instead of using the insert image button above.

Reply

Answer 5

Apr 18, 2014 6:45 PM in response to bradyfans

I can tell from your screen shot that you did not place your lookup information in a separate table as I advised, but since I can't see your expression I can't tell why it isn't working. I only know that the expression I gave you won't work in this situation. It also appears that you aren't familiar with the purpose of Header rows and columns. This is something you can study in the Numbers Help guide.

Probably the best thing now would be for you to start another document and follow my directions word for word. Once you get that working, if there are other elements to your data and other calculations you would like to make, we can add them.

Jerry

Reply