Showing posts with label Human Services. Show all posts
Showing posts with label Human Services. Show all posts

Sunday, January 8, 2023

Using OpenAI's ChatGPT to Update My Year in Pixels Excel Template

I've been seeing more and more videos on TikTok about this mysterious new artificial intelligence program called ChatGPT. People are saying it will eventually replace Google search. But what really got my attention was a demonstration of ChatGPT writing VBA macros for Microsoft Excel. Now I knew I had to try it for myself. 

I made a Year in Pixels Excel template to track my daily mood back in 2018 but haven't updated it lately. Since it's the start of a new year, I recently received some new comments and questions about how to use and modify the spreadsheet so I figured now would be a great time to update it and add a few new features. Then I had an idea - why don't I use this opportunity to update my mood tracking Excel template to try out ChatGPT? Let's give it a go!


How to have cell fill color conditional formatting change based on the color of another cell?

The request from a user that got me thinking was "how to easily change the colors in my year in pixels sheet?" An experienced Excel user could probably ascertain the colors are applied by conditional formatting formula rules. However, for users who are not Excel savvy, it's not very obvious and there should be an easier way to change the colors. As far as I know, there is none, or at least no easy way in my current version of Excel, to have the fill color in the conditional formatting reference another cell's color. So instead, I will have a macro update the conditional formatting rules at the click of a button. Let's jump over to ChatGPT.

How does ChatGPT work?

Here's the answer after asking it directly: 

GPT (Generative Pre-trained Transformer) is a type of language model developed by OpenAI that uses a transformer neural network architecture to generate text. GPT is trained on a large dataset of text and uses this training to generate new text that is similar in style and content to the training data.

ChatGPT is a variant of GPT that has been fine-tuned on a specific task, such as having conversations with humans. To fine-tune GPT for this task, ChatGPT is trained on a large dataset of conversation data and learns to predict the next word in a conversation based on the words that came before it.

When you input a message to ChatGPT, it processes the message and uses its knowledge of language and conversation patterns to generate a response. It does this by predicting the next word in the conversation based on the input it has received and the context of the conversation so far. The output of ChatGPT is then a response that is generated based on this prediction.

One thing to keep in mind right off the bat: ChatGPT is not always right. It says it right there on the main page under limitations: may occasionally generate incorrect information; may occasionally produce harmful instructions.
For example, I asked "list the ten tallest roller coasters in the world" which should be fairly easy and instead it produces a list that is incorrect and doesn't even make sense.


Not a great start but let's see how it does creating a macro from scratch.

Creating Custom VBA Macros Automatically with ChatGPT

I wasn't sure how specific I needed to be so I decided to err on the side of caution, plus I thought the more detailed I am the less modification I'll probably need to do later. Now I ask ChatGPT: "create a vba macro to make a new conditional formatting rule where range is C4 to N34, if cell value = 5 then change the fill color to the same color as in cell P12." Then it began typing and my jaw dropped.

I copied and pasted the code into Excel VBA editor and it worked! One thing I forgot is this macro will just add new rules applied to the same range. I need to delete all the existing formatting rules before adding new ones, so I ask ChapGPT:


I added the above code to the earlier response and linked it to a newly added button on my sheet. Now a user can change the fill color of a cell, click the button, and the fill colors update automatically to match.

If you want to see all this play out in real time, watch the video I recorded below:

Thoughts on the Future of ChatGPT

It's awesome that it doesn't just spit out the code, but it also suggests how you might need to modify it AND tells you how to run the macro as well. Even though I've only asked it to make simple macros, I already see how this program could save a lot of time.

I'm not done experimenting but so far ChatGPT seems like a much better option over Recoding macros or Google searches that might take you a few tries to find exactly what you're looking for. Especially when you can get custom code on the fly. On one hand, I feel a little obsolete, but on the other I also don't think ChatGPT will completely be replacing programmers just yet as you can see I still had to understand the code and modify it to fit my exact needs.


Download my Year in Pixels template for free here and try it for yourself. Open the macro editor to view the final codes written by ChatGPT.

Thursday, February 4, 2021

COVID-19 Vaccine Distribution Tracking Template

Not sure if this is helpful to anyone but I made a COVID-19 Vaccine Distribution Tracking document in Excel. It’s hard to make a spreadsheet that you’re probably not going to use personally yourself. But I tried to envision being in management of a company or nursing home or school that needed to:

 (1) prioritize which employees or residents or teachers should get the vaccine first and

 (2) track which people have actually gotten the vaccine and when.

 

covid-19 vaccination tracking spreadsheet

What Order to Distribute the Vaccine

The first item I tackled was how do you decide in what order to give out the vaccine. Knowing that supplies are limited, who gets the first dose?

I use a scoring system to decide the priority order: the highest score is first in line to get their shots.

 Here’s how the score is calculated:

  1. Age: the score starts with the person’s age. 30 years old = 30 points. The older population automatically has a higher score.
  2. Age multiplier: I made up a tier system based on age. Based on earlier data I’ve seen, the chance of dying from COVID if you’re 59 years or younger is only around 1% or less, but this jumps up to nearly 20% if you’re 80 or older. In my scoring system, those 60 and older get varying amounts of “bonus” points added to their score depending on what age range tier they fall into.
  3. Priority person: Yeah, you could just rank vaccination order by age and be done with it but I didn’t want age to be the only limiting factor. There are other variables that I wanted to account for. I added the ability for a person to be marked as “high priority”, maybe due to immune deficiency or because they’re a first responder, or pregnant – whatever you want it to be. This adds 11 points to their score to help bump them up in the distribution order. Again, the spreadsheet is easily customizable so you can change the bonus to whatever value fits your needs.
  4. Male or female: This might be controversial, but I added a segment to the scoring where males get a slight score increase over females. From the data I have (and please send me any new information if you think I’m wrong) males have a ~3% higher death rate from COVID than females (women are generally better at fighting off infections I’ve heard) so they get +3 point bonus while females get none. Of course, feel free to change this setting to make it what you want. Give woman 1,000 bonus points and men 0 – it’s up to you and can be easily changed in the spreadsheet.

The total score is calculated by adding up the age, the age tier multiplier, the male/female bonus, and the priority bump. The biggest factor is age, as that is the biggest factor of death rate. While getting the highest score means you’re first in line to get the vaccine, that also means you have the highest risk of dying from COVID.  I then use the RANK function in Excel to rank the individuals from highest to lowest score. There’s your priority list of who gets the vaccine first.


Vaccine Distribution Tracking

The next component, after deciding the vaccination order, is to then track if these people actually got the vaccine. Complicating matters, there are (right now) two different vaccines being distributed in the US requiring two separate doses and the time between doses is different. Each vaccine also has a minimum age requirement that is different so there is a formula to make sure the individual is old enough to get the selected vaccine. If they’re not eligible, meaning you’re too young, your score automatically goes to 0 and I use condition formatting to turn the cells black to show the individual shouldn’t be scheduled to get a shot. Once you get the first dose, enter the date and the spreadsheet will tell you when the earliest is you can get the second dose based on which vaccine you got. Finally, you can quickly see how many of your employees or residents have received the first or second doses of the vaccine.

 Download the spreadsheet here: https://gumroad.com/l/covid-tracker

I’ve been wanting to put my Excel powers to good use rather than just making Super Bowl squares or football related spreadsheets. Not sure the legality of it but if I was in charge of a company with employees working from home I would want to know when it is safe to bring that back into the office. Try it out and let me know what you think!

Monday, October 15, 2012

Travel Authorization Request Form Template

travel request template
Today I am going to share with you an Excel spreadsheet template I use every time I need to travel for my day job - my travel authorization request form. Many companies today use travel request forms to help log and keep track of employee’s travel. Yes, even I still have to get permission from the bean counters before I have approval to make travel arrangements!

My request for travel authorization form is very simple and easy to use. It’s extremely light - there are no formulas involved. If you’re setting this up for your own business you can also choose different per diem pay rates if the traveler will be paid for food on a daily basis.

Below is a link to a free download of my Travel Form spreadsheet. Please feel free to let me know if you have any questions. Check out our Downloads page for more free Excel spreadsheets or the project management page for other templates.

Travel Authorization Request form.xls

My travel authorization form is also included in my new project management template which you can download for free when you sign up for the Excel Spreadsheets Help email newsletter.

What about you? Do you have your own travel reimbursement form? Does your company require a travel authorization sheet to be filled out?

Nick
Travel Authorized

Sunday, July 22, 2012

Glasgow Coma Scale Chart


The Glasgow Coma Scale (or GCS) aims to give a reliable way of recording the conscious state or a person for assessment. Many of my recent posts have centered on sports (especially football spreadsheets) so today I thought I would go in a completely different direction. One area I have never touched on in this blog is the medical profession and how Excel is used in hospitals and rehabilitation centers. The Glasgow scale is used in the field of therapeutic recreation, among others.

The GCS scale is broken down into three main behaviors or activities: Eye Opening (E), Motor Responses (M), and Verbal Responses (V). Each of these behaviors can be rated based on some predefined criteria. Each criterion is given a point value from one to six. To obtain the final GCS coma score simply add the results from the three previous behaviors (Summed Glasgow Coma Scale Score= E+M+V). The final score should fall between 3 and 15 points where a score of 8 is the midpoint. Any Glasgow Coma score below the midpoint shows serious damage.


I have made a blank template of the GCS scale which you can download and print out for free.


On a related note, you can also read about and download a template for the Ranchos Los Amigos Scale which is also known as the Level of Cognitive Functioning Scale or LCFS. LCFS is a medical scale used to assess individuals after a closed head injury.

For more information on how to pass the NCTRC exam to become a CTRS visit rectherapycenter.com

Is this information helpful to you? Would you like to see more health and human services related Excel templates and forms? Please feel free to comment below or Tweet me and let me know your thoughts. Your feedback will only help make this blog better.