[X]My Assistant

[jarvan]

I thought I had posted this question, but alas, don't see that I ever did.

Someone built an Excel table for me and it is pretty much perfect, but the way it is built. But...

What I need is for my columns bearing Yes/No options to calculate the percentage of time "yes" is answered. I hope that makes sense. The way the table is constructed right now, it just gives me a raw number/count.

I have attached the spreadsheet for you to see what I am talking about. Please see the coding in row 2 above the corresponding column with Yes/No options.

This is a dynamic spreadsheet (hope I termed that correctly), no real end date (even though the person who created it used a hypothetical 1 year period) and I need the numbers to recalculate when I input data into a cell.

I have no Excel coding experience and appreciate any assistance you can give me.

I hope this request is feasible, but if not, just let me know. The post requires me to choose a version of Office and we are running 2016. That was not a choice in the dropdown.

If this works, it will be awesome!

Thanks in advance!

Jen

Attached File(s)

 Tracker.zip ( 12.44K )Number of downloads: 4

 

[ADezii]

I'm pretty sure that there is an easier way but in the meantime, to create a Formula in a Cell that returns the Percentages of Yes's in the Hospital Antibiotics Column:

CODE

=COUNTIFS(Table_Intake[Hospital Antibiotics],"yes")/COUNTA(Table_Intake[Hospital Antibiotics])*100

This post has been edited by ADezii: Feb 11 2019, 04:53 PM

Attached File(s)

 Tracker_Revised.zip ( 10.69K )Number of downloads: 4

 

[Dan Dungan]

I used a formula:

For Hospital Antibiotics:
=IF(COUNTIFS(Table_Intake[Hospital Antibiotics],"yes")>COUNTIFS(Table_Intake[Hospital Antibiotics],"No"),COUNTIFS(Table_Intake[Hospital Antibiotics],"No")/COUNTIFS(Table_Intake[Hospital Antibiotics],"yes"),COUNTIFS(Table_Intake[Hospital Antibiotics],"yes")/COUNTIFS(Table_Intake[Hospital Antibiotics],"No"))

For Diarrhea Present:
=IF(COUNTIFS(Table_Intake[Diarrhea Present],"yes")>COUNTIFS(Table_Intake[Diarrhea Present],"No"),COUNTIFS(Table_Intake[Diarrhea Present],"No")/COUNTIFS(Table_Intake[Diarrhea Present],"yes"),COUNTIFS(Table_Intake[Diarrhea Present],"yes")/COUNTIFS(Table_Intake[Diarrhea Present],"No"))

[jarvan]

Thank you! I am learning so much!

Have a great day.

Jen

[dflak]

ADezii's formula is simpler except that I'd drop the * 100 and format the cell as a percent.

[jarvan]

I would format as a percent if I knew how to format.

[dflak]

It's a basic skill. Do a web search for Excel Cell Format. I'm sure they will do a better job explaining it than I can.

[ADezii]

1. You can either build the Percentage directly into the Formula itself or Format the Cell through the Interface. The Interface option you can reference yourself, but as far as the Formula, here is another option:
2. Example:
   
   CODE
   =TEXT(0.625,"00.00%")
3. produces
   
   CODE
   62.50% (2 decimal places)
4. In your specific case for 'Hospital Antibiotics':
   
   CODE
   =TEXT(COUNTIFS(Table_Intake[Hospital Antibiotics],"yes")/COUNTA(Table_Intake[Hospital Antibiotics]),"00.00%")
5. Hope this helps.

This post has been edited by ADezii: Feb 12 2019, 01:49 PM

[jarvan]

Thank you very much. I realize this may be a "basic skill". I am learning.

[ADezii]

Learning is a never ending process.

[dflak]

I was not trying to offend, I was merely pointing out that there are sources that can lay a better foundation than I can.