UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Excel Table Help Needed Please, Office 2013    
 
   
jarvan
post Feb 11 2019, 03:47 PM
Post#1



Posts: 4
Joined: 8-February 19



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)
Attached File  Tracker.zip ( 12.44K )Number of downloads: 4
 
Go to the top of the page
 
ADezii
post Feb 11 2019, 04:48 PM
Post#2



Posts: 2,178
Joined: 4-February 07
From: USA, Florida, Delray Beach


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)
Attached File  Tracker_Revised.zip ( 10.69K )Number of downloads: 4
 
Go to the top of the page
 
Dan Dungan
post Feb 11 2019, 04:50 PM
Post#3



Posts: 297
Joined: 20-July 10
From: chatsworth, ca


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"))

--------------------
Dan
Go to the top of the page
 
jarvan
post Feb 11 2019, 04:58 PM
Post#4



Posts: 4
Joined: 8-February 19



Thank you! I am learning so much!

Have a great day.

Jen
Go to the top of the page
 
dflak
post Feb 12 2019, 12:25 PM
Post#5


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


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

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jarvan
post Feb 12 2019, 01:27 PM
Post#6



Posts: 4
Joined: 8-February 19



I would format as a percent if I knew how to format. smile.gif
Go to the top of the page
 
dflak
post Feb 12 2019, 01:29 PM
Post#7


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


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.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
ADezii
post Feb 12 2019, 01:48 PM
Post#8



Posts: 2,178
Joined: 4-February 07
From: USA, Florida, Delray Beach


  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
Go to the top of the page
 
jarvan
post Feb 12 2019, 04:41 PM
Post#9



Posts: 4
Joined: 8-February 19



Thank you very much. I realize this may be a "basic skill". I am learning.
Go to the top of the page
 
ADezii
post Feb 12 2019, 04:45 PM
Post#10



Posts: 2,178
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif Learning is a never ending process.
Go to the top of the page
 
dflak
post Feb 12 2019, 05:25 PM
Post#11


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


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

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2019 - 10:15 AM