# formula comparing data in mulitple columns

#### jimerinbowers

##### New Member
If column BB contains "discharge to: info" for patient (i.e., "SNF", "Home", "Expired", etc) and column J contains "age: info" for patient (i.e., "55", "92", "16", etc), what formula (SUM, AVG, etc) do I write that considers only patients' "ages info" who have "expired" (i.e., BB4 contains "expired" and J4 contains "44"; BB7 contains "expired" and J7 contains "17"; etc). I need a formula that compares ONLY LIKE DATA from cells ("expired") in one column (BB, for instance) to data in cells in other columns (J or K or AA, etc) but on the SAME row (4). Probably a standard, elementary question but I am new to this and cannot find the proper "HELP" in the program or other materials at hand. Can you assist? Thank you.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### plettieri

##### Well-known Member
Hello jimerinbowers
Welcome to the Board

It may look like would use a cominiation of IF, Vlookup, or some others...

Care to post a sample of your data on a spreadsheet and the expected outcome you would like to see...someone here will solve this real quick.

You can use the Download Colo's cool utility for displaying your Excel Worksheet on the board...see below this message box..

pll

##### MrExcel MVP
On 2002-10-20 15:30, jimerinbowers wrote:
If column BB contains "discharge to: info" for patient (i.e., "SNF", "Home", "Expired", etc) and column J contains "age: info" for patient (i.e., "55", "92", "16", etc), what formula (SUM, AVG, etc) do I write that considers only patients' "ages info" who have "expired" (i.e., BB4 contains "expired" and J4 contains "44"; BB7 contains "expired" and J7 contains "17"; etc). I need a formula that compares ONLY LIKE DATA from cells ("expired") in one column (BB, for instance) to data in cells in other columns (J or K or AA, etc) but on the SAME row (4). Probably a standard, elementary question but I am new to this and cannot find the proper "HELP" in the program or other materials at hand. Can you assist? Thank you.

What is the question?

#### plettieri

##### Well-known Member
Hello jimerinbowers

I read your question again and tried to put a spreadsheet summary of what you might be asking. If this is the case, I have presented two sections ...one with raw data and then that range sorted.....by "Discharge" field first and "Age info" second field. You should be able to do this by highlighting the entire range including the names of the column, then data, sort, sort by selection....

If this is not what you are looking for, please clarify your question.

pll
Book1
FGHIJKLMNO
13Raw dataAfter Sort
14Discharge toAge InfoDischarge toAge Info
15SNF55Expired16
16Home92Expired17
17Expired16Expired44
18Expired44Expired55
19Home54Home54
20Expired55Home92
21Expired17SNF55
22
23
24
Sheet6

#### jimerinbowers

##### New Member

pll:
I know this would be easier to answer if I downloaded the spreadsheet program but I am a little leary of doing that at this time. See if this makes sense to you: A sample group of...
Only those patients who are expired (for instance) are to be considered in any formulation. A number of different category columns describe other characteristics of the patient (Age, Length of Stay, various periods of time between certain administration events concerning patient, attending doctor, nursing station, etc.). By just considering "expired" patients, I can then calculate formulations pertinent to expired patients alone separate from the entire pool of patients hospitalized over a period of time (Not everyone dies here ;o)). I did not want to sort "expired" to another worksheet and use the formulas from the main spreadsheet for that group. Rather I wanted to add another column to the to the main spreadsheet that would include information regarding "expired" patients only along side the column(s) that contained calculations for all patients. Some of the categories by row (from columns a:d--separated from the section containing data entries of each patient--columns f:bz) would be Average length of stay, days from consult to expiration, days from admission to DNR code status, and a multitude of other categories. I consider averages, sums, percent of category responses, percent of patient considered, etc.
Does this make sense? Somehow I need to isolate groups of patients by unique characteristics (i.e., "expired") appearing in one column and relate that info in various ways to the data that appears on those patients that has been inputted in cells from other columns. (In all cases ONLY considering data that appears on the same ROWS of the column that contains the cell with the unique characteristic (as exampled, "expired"). There are fify +/-columns of various categories concerning each patient. Sorry if this is tedious. I appreciate your help. God bless.

#### plettieri

##### Well-known Member
Hello jimerinbowers

I think to give you as many options as possible and to make your question a bit more clear.... a sample representaion of your data and your expected results would be very helpful. A short display is all that is needed.

pll

#### plettieri

##### Well-known Member

Hello jimerinbowers

I have received your PM with the worksheet attached. It is in the best interest to try to post any details and sample worksheets to this board, as this is a sharing and learning forum for all participating. I took the liberty to extract a sample portion of your large worksheet to help identify your question to many others much more capable than I.

I will try to restate your question...You want to compare/collect from each of the deceased persons selected data that you have arranged in columnar format. For those reading this inquiry the full worksheets is approx 55 columns and as many rowws down.

A possible solution might be to autofilter the range you would like to compare...In that process you can select on any of columns you select in the filter. Is this the direction would are looking for. If not, please restate your question referring to the spreadsheet posted... and the outcome you expect so others might give some better suggestions.

good luck
pll
Test.xls
BCDEFGHIJKLM
20
21DateMedical Record #Acct #NameAgeGenderEncountersConsult HoursDischarged toAge Exp
22Patients Expired Over 30 DaysApr02xy101x190F11.5Expired90
23??????????Apr02xy102x269F22Hospice at HomeFALSE
24??????????Apr02xy103x312M2028Expired12
25??????????Apr02xy104x422M11Own HomeFALSE
26??????????Apr02xy105x585F22SNFFALSE
27??????????Apr02xy106x663F12Expired63
28??????????Apr02xy107x70M11.5Expired0
29??????????Apr02xy108x880M11Hospice at HomeFALSE
30??????????Apr02xy109x970F11.5SNFFALSE
31??????????Apr02xy110x1074F1822Expired74
32??????????May02xy111x1180F21.25Expired80
33??????????May02xy112x1245M11.5Own HomeFALSE
34??????????May02xy113x1370F55SNF/HospiceFALSE
35
Sheet2

#### plettieri

##### Well-known Member
I appologize, but I would like to bump this inquiry up as additional feedback would be helpful to the sender

pll

#### Yogi Anand

##### MrExcel MVP
Hi jimerinbowers:

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-20 21:40

Replies
14
Views
350
Replies
1
Views
377
Replies
0
Views
561
Replies
1
Views
86
Replies
3
Views
105

Excel contains over 450 functions, with more added every year. Thatâ€™s a huge number, so where should you start? Right here with this bundle.

1,163,867
Messages
5,834,079
Members
430,260
Latest member
MANICX100

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back