08/17/2017 – Grocery list from meal planner

I’m usually the meal planner in my house. It’s not due to my wife not being able to do it, but rather that she does not like to and I do. I’m also someone who enjoys making spreadsheets for everything. So, I thought it may be fun to create a spreadsheet that would allow me to plan dinners for the week. And, the spreadsheet would generate a list of ingredients for the dinners, so I would know what to buy. Here is how I did it.

First thing was to figure out a list of dinner options. For this example, I will use:

  • Cheeseburgers
  • Spaghetti
  • Chili dogs
  • Nachos

Now that I have a list of meals, the next step was to figure out the ingredients. For my spreadsheet, I kept is simple. I just wanted a grocery list, not a recipe. For example, for Cheeseburgers, I put down:

  • Frozen hamburger patties
  • Hamburger buns
  • Cheddar cheese
  • Tomato
  • Onion
  • Lettuce
  • Mayonaise
  • Mustard
  • Ketchup

Now that I have the data, it was time to create the spreadsheet.

For this tutorial, I created my spreadsheet in Google Docs. The formulas that I am using should work in any spreadsheet program. I tested them out in Excel 2013 before using them in Google Docs.

Also, for this tutorial, I am only using one tab on workbook. In the version I use privately, I am using three separate tabs. I recommend arranging things in a way that works for you.

On the spreadsheet (columns F-I), I first fill in the name of each meal, then list the ingredients underneath the name. For example, here’s “cheeseburgers”:

Cheeseburgers
Frozen hamburger patties
Hamburger buns
Cheddar cheese
Tomato
Onion
Lettuce
mayonnaise
Mustard
Ketchup

When filling in meals with a space, like “Chili dogs”, I use an underscore in place of the space. For example, “Chili_dogs”. We are going to use these in an drop-down list later, which will not allow spaces.img1

Once the meal names and ingredients are added, it is time to make them into lists. In Google Docs, it is as simple as selecting all the cells you want on the list, right-click, then select, “Define named range”. For Excel, it is almost the same, but the option is, “Define name”. I make the meal names into a one list and call it, “Meals”. Then, I make each list of ingredients for each meal into a separate list. So, the list of ingredients for the cheeseburgers is a list, called, “Cheeseburgers”.

The next thing I created was the days of the week (Monday, Tuesday, Wednesday, etc.), listed top to bottom (column A, rows 2-8). I selected the cell next to the first day (cell B2: “Monday”), right-clicked, and selected “Data validation”. I made sure, “List from a range” was selected and typed “Meals” in the box. Then, I copied that cell, selected the cells next to rest of the days (“Tuesday”, “Wednesday”, etc.), right-clicked, and selected, “Paste special”, and “Paste data validation only”. This made it so I could choose one of the meals from a drop-down menu for each day.

img2

For the next part, I needed to know how many items were in the longest ingredients list. For the example, “Cheeseburgers” had the longest with nine items. I then put the names, “Shopping List” and each day of the week (Monday, Tuesday, etc.) across the top of eight columns (columns L-R). Under the name of the day of the week (in row 2), I put “=”, then selected the cell containing the drop-down menu next to that day (from column B). This would make it so when I selected a meal next to a day, it would also populate this cell. For example, if I selected “Chili_dogs” for Monday in cell B2, it would also populate cell L2 with “Chili_dogs”.img3

In the row under the cells that I put the “=” (row 3), I put the formula, “=INDIRECT(L2)”, “=INDIRECT(M2)”, “=INDIRECT(N2)”, etc. The “L2”, “M2”, etc. refers to the cell above it. This would make it so when a meal was selected next to the day of the week and that name would populate in row 2, the ingredients for that meal would populate starting in row 3. or example, if I selected “Chili_dogs” for Monday in cell B2, it would populate cell L2 with “Chili_dogs”, and then, starting with cell L3, the ingredients for “Chili_dogs” would populate.

**Note** For Excel, I had to change the formula a little. Google Docs displayed the entire ingredients list, but Excel only displayed one row. So, I changed “=INDIRECT(L2)” to “=INDIRECT(L$2)” and copy and paste the formula down fifteen rows.

Remember when I said that I needed to know how many items were in the longest ingredients list? Well, this is where that knowledge will come in. In my example. “Cheeseburgers” had the longest ingredients list with nine items. So, starting with cell L3, I counted down ten rows to cell L12. I selected all those cells, right-clicked, then selected, “Define named range”. I named the range, “List1”. Then I repeated the same process for rest of the columns, resulting in “List1” to “List7”.

The code that I will use next, I got from Get Digital Help.com.

Lastly, we are ready to generate the shopping list. I create a column with the name “Shopping List” on the first row (D1 in the example). On the second row, I put the following code:

=IFERROR(INDEX(List1, (ROWS(D1:$D$1))),
IFERROR(INDEX(List2, ROWS(D1:$D$1)-ROWS(List1)),
IFERROR(INDEX(List3, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)),
IFERROR(INDEX(List4, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)),
IFERROR(INDEX(List5, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)),
IFERROR(INDEX(List6, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)),
IFERROR(INDEX(List7, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)),
“”)))))))

After typing the last “)”, I press, at the same time, “CTRL”, “Shift”, and “Enter”. In Google Docs, it will add “ArrayFormula” and an extra “)”. In Excel, it will add “{” and “}”. I then copy the code, and paste it down the column for seventy rows (10 ingredients * 7 days of the week).

Currently, I have not figured out how to automatically remove all the empty cells or duplicates from the “Grocery List” row. I know it is possible to copy and paste-special the values to another row, where I can remove the cells and duplicates.

Advertisements
Posted in Uncategorized | Leave a comment

08/10/2017: How I would fix the A.C.A. (part 2)

As promised, this post is going into ideas on how hospitals and insurance companies can save money. Because I do not work in the medical field or for an insurance company, my ideas are purely of an outsider. So, they may not be feasible. I’ll start with hospitals first.

My first recommendation is to set up a tiered system of care providers. In other words, have patients see a registered nurse or nurse practitioner instead of a doctor, when possible. For example, if you are going in for your yearly physical, do you really need to see a doctor? Not unless there is something else going on. Which, at that point, you would be seen by a doctor.

Next, I would push to allow for foreign trained doctors to have an easier and quicker path to becoming registered U.S. doctors; if they choose to. My proposal is to first establish a department that could verify medical schooling and degrees of the doctors. Once it is established that they were medically trained, they would need to take a test to determine how much medical knowledge they have. They would be granted a temporary license, which would expire in one year. Depending on the outcome of the test, that license which would either put them at the level of a registered nurse or nurse practitioner. This would allow for them to start working in the medical practice, so they could gain knowledge on the U.S. medical system. When it came time to renew their license, they could either stay at they level they are at or choose to test to see if they could advance.

This recommendation is for both hospitals and insurance companies, and that is, to push patients to have preventative care. For hospitals, it would mean offering advice and assistance on programs to patients for getting and staying healthy. For insurance companies, it would be offering lower premiums and/or coverage for patients that participated in preventative measures. Or course, this would help reduce the amount of patients with preventable medical issues.

Lastly, we need to better educate the general population on how to live healthy. For too long, what is healthy or not has been dictated by who stands to gain the most profit. With the rise of the internet, some people are becoming self-educated and dispelling the dis-information. However, as we’ve seen with the “anti-vaxxer” movement, it only takes a small percentage of the population to cause a medical crisis. Therefore, there needs to be a department that both educates children in school, but also the general public on these myths.

Well, that is my recommendations on the A.C.A. As you noticed, I did not talk about Medicaid. Well, that is because my proposals are bringing the cost of health care coming down, which will also reduce the amount spent on Medicaid. And, if the costs can be brought down low enough, then we may even be able to have an affordable “universal healthcare” system like other countries.

Posted in Uncategorized | Leave a comment

08/08/2017: How I would fix the A.C.A. (part 1)

When I was planning this blog post a few weeks ago, the Affordable Care Act (A.C.A.) was in the headlines. At the time, it was being proposed to repeal and replace it, later followed by repeal, and followed by that with creating legislation that would cause it to fail. Since before its inception, the A.C.A. has been on the political chopping block.

The politics started when the A.C.A. was nicknamed, “Obamacare”. So, anyone caught supporting it would automatically be associated with (former) President Obama. Thus, any “fixing” would also be seen as also supporting Democrats. So, the very first thing I would fix about the A.C.A. is to re-brand it. By calling it something totally different and ensuring that the new name is not linked to either party will keep it from being a political issue. Don’t kill it, because there are some good things about the A.C.A. (like the pre-existing conditions clause) that should not be stopped. Even better, have a contest to determine the new name. Let the public decide. “Obamacare” is dead and from its ashes rises…to be determined.

Despite the name change, the biggest issue of the A.C.A. is the “affordable” part. Most of the complaints I hear about the A.C.A. is the cost of insurance, such as that it is too high. Part of the agreement with the insurance companies was that requiring everyone to purchase insurance would drive down the cost. The idea was that having people pay for insurance that rarely use it would off-set the cost of people who use the insurance on a regular basis. But, there was not as much of a “reduction” as there was a slower cost increase.

Reducing costs of insurance is a very complex issue. We can’t simply tell the insurance companies to not charge as much. They are still companies and that would make them loose money or even go out of business. Which would lead to loss of jobs and deter anyone from ever going into business as an insurance company. I was pondering this part a few weeks ago when I caught the episode of Adam Ruins Everything, called, “Adam Ruins The Hospital”.

For those not able watch the episode, Adam points out how the costs of medical care in the U.S. was once a fraction of what it is now. Furthermore, the U.S. pays anywhere from 30-60% more for equipment or procedure than other developed countries. Of course, this is so the hospital can make a profit. But, anyone who has worked for or been to a hospital may notice, they are not making very much of a profit. So, why are they charging so much of a mark-up?

This is where I tell you about a document called, “The Chargemaster“. Other than California and Maryland, this is a document that the public does not get to see. It lists how much everything costs for that hospital. And, it breaks down the cost by how the patient is paying, be it cash or insurance. It is also where we see that prices are inflated in order to give discounts to patients with certain insurance companies. You see, by making a hospital “in-network”, insurance companies cause patients to choose one provider over another. And thus, they want a discount for doing that. Logically, in order to give that discount and still make enough profit to stay in business, hospitals inflate their prices.

The first thing I would do to reduce costs is to eliminate “in-network” and “out-of-network” coverage for insurance. The second item I would do is make hospitals have fixed rates for both insured and not-insured patients. This would stop the discount mark-ups. The third item would be to require hospitals to post their prices on-line, like California does. This would cause competition, which would also reduce costs.

Just like with the insurance companies, I don’t want hospitals to loose profits, so in my next post, I will explorer how to they can reduce costs.

Posted in Uncategorized | Leave a comment

07/21/2017: Slippin’ accent

Despite being born in the Seattle area, from around age four to age ten, I lived in the northwestern Missouri area. During that time, I learned to speak and ended up with a bit of an accent. Until I moved back to Washington state, I did not realize I had an accent. But once it was pointed out to me, I started trying to get rid of it.

One thing I started doing is to enunciate in a “metered” cadence. Not too fast, not too slow. This cleared up most of the accent. The next thing I did, was try to not use contractions when speaking. No more, “can’t”, “they’re”, or “don’t”.

Despite only having the accent for six years and trying to get rid of it for over a decade, you’d expect it to be gone. I thought that too, but when I started doing tech support back in 1999, during a stressful call, it was brought to my attention that the accent was still there. I had started speaking quickly and it came out. Even now, almost thirty years later, I still get the occasional comment that people can hear it when I am tired or stressed.

So, why does the accent persist? Well, my theory is that I am still being exposed to it via TV, movies, and music. I assume this, because I’ve caught myself speaking with the accent after hearing someone else use it.

Just to be clear, I am not ashamed of my accent. However, I know that it makes it difficult for people not from that region to understand me. Also, given that I have not lived in the “south” for a long time, it does not represent who I currently am. Again, not ashamed of living in that area, just trying not to give people a misconception of me. Since I have not lived there a decades, I would not be a good reference to what it is currently like there.

Posted in Uncategorized | Leave a comment

06/26/2017: Satellites

Back in 2007, I was working at Amgen in Seattle. We were in a building that had been built in 2004. In one of the conference rooms, there was a large monitor and camera, so that people in the Seattle office could have a meeting with those in other Amgen locations. The idea was to be able to let everyone meet “face-to-face” without having people physically travel. Unfortunately, because the technology was still “new”, most of management preferred not to use it.

After my department closed with Amgen, I went to work in the internal help desk at Safeco Insurance. To reduce the cost of renting office space, Safeco had set up some employees across the country with computers and internet that they used solely for work. Safeco would be providing a computer for the employee to use if they were in an office, so that cost was already there. So, all that Safeco had to figure out is, would it cost more to rent/furnish office space versus paying for the employee’s internet connection. When I left Safeco, the company was starting to remove the “work-from-home” program, due to concerns that the company could not actively supervise employees to ensure that they were working.

Currently, I work in downtown Seattle. There are a lot of people who work in downtown Seattle. Every day, we all commute to and from work. As Seattle continues to expand, the commute gets longer. Downtown Seattle is has water on three sides, which creates a “bottleneck” into the city from anyone commuting from the west, north, or east. South of Seattle is no better, because it was where most residents have lived the longest. It is also where the two main airports are located (Sea-Tac and Boeing Field). That makes it difficult to find land to build more roads or highways.

Another issue with the expanding downtown Seattle is the surge in housing costs. The cost for either renting or buying close to downtown Seattle has become almost unattainable for most families. In order to find an affordable place to live, workers are moving further and further away, making commuting longer and longer. The Seattle city council is even imposing rent restrictions to try to help, but they should not have to do that. My prediction is that soon, downtown Seattle will see a huge reduction of workers willing to work in restaurants, coffee shops, and stores. And/or, those businesses will need to severely hike up prices, just to keep workers.

Of course, I have a solution to all of this; satellite offices. These are smaller offices that are opened in cities close to Seattle. Places like Kent (south), Everett (north), Redmond (east), and Bremerton (west). They are close enough that if needed, workers can still travel to downtown Seattle for work that day.

As the two stories I wrote at the beginning of this post point out, the technology has existed to make this possible. In fact, I can make a video call from my desk phone to (almost) anyone else in any of our offices. So, why do I physically need to be in a conference room with them? And given that they would still be in an office environment, there should not be a concern that workers would be unsupervised.

As for the benefits, the obvious one would be the reduction in commute times. And, that would also spur the movement of people to move away from downtown Seattle, which would reduce housing costs in that area. It would also increase the revenue of businesses around the satellite offices that offer goods and services, which could then afford to hire more workers. But, the companies that provide the satellite offices would also see a financial benefit.

Along with the cost of housing, the cost of office space in downtown Seattle also is increasing. I’m guessing that the same square footage for office space in the cities I mentioned are a fraction of what it would cost. And, with more and more businesses moving to the satellite office model, just like with housing, the prices would also come down on office space. Of course, as more businesses also move to the satellite model, the office space in those cities will go up, but still would not be as much as downtown Seattle.

Just like with all new ideas, in order for any of this to happen, it would take some stepping back from what businesses have traditionally done. That is the most difficult part, because decision makers often stick with the ideas that have been tried and proven, which is understandable. However, companies that require face-to-face with the general public, like banks/credit unions, insurance companies, and retail companies already follow this model. So, it is not a new concept, just one that has not been fully adopted by all companies.

Posted in Uncategorized | Leave a comment

06/22/2017: Building my digital library (part 3)

In my previous posts, I have:

  1. Downloaded the necessary software.
  2. Figured out how to obtain the shows I want.
  3. Figured out the naming convention for the shows.
  4. Figured out how to make the shows into a 16:9 resolution.

Now is time to convert and rename the shows.

I start by opening Avidemux. Clicking on the blue folder icon (Open Video), I find and open the .mkv file that kmttg downloaded and converted. Under “Video Output”, I choose “Mpeg4 AVC (x264)”. Under “Audio Output”, I choose “AAC (Faac)”. Under “Output Format”, I choose “Mkv Muxer”. Then, I click on the “Filers” button under the “Video Output” section.

In the “Available Filters” column, I right-click on the “Add Borders” filter and select “Add”. This is the filter that will add the black borders around the picture. Once I have that information in, I click on “OK”.

Scrolling down the “Available Filters” section, I find, right-click, and add the “swsResize” filter. I change the Aspect Ratio Source and Destination to “16:9”. In the “Resize Dimensions” section, I try to get as close to 1280 width and 720 height as it will let me get. It can only resize up to 200%. So, if the video you added the black borders to is 624×351 (16:9 x 39) or lower, you will not be able to reach 1280×720. Don’t worry if you cannot reach, just get as close as you can.

Last filter will be the “Noise” category. Find, right-click on, and add the “Mplayer Denoise 3D HQ” filter. Don’t change any of the settings, just click “OK”. This will smooth out the picture so that it does not appear pixelated or stretched. Close the filter window and go back to the main window.

Using the slide below the video, I get as close to the beginning of the show as I can. Then, using the left and right arrows for more accuracy, I find the right frame. I click on the “A” icon (Set start marker) to mark that spot. Next, I use the slide and arrow keys to find the end of the program. I click on the “B” icon (Set end marker) to mark that spot.

Clicking on the disc icon (Save Video) will open a window that will let you choose what you want to call the video and where you want to save it. I use the naming convention that Plex recommended and save it in the folder where I plan on putting all my videos that I want Plex to play. Depending on the video size and the computer’s processing power, it may take anywhere from ten to ninety minutes to convert.

Once I have at least one video ready, I can add it to Plex. Using these instructions, I create a Library for each TV series. As long as you used the correct naming convention, Plex should be able to recognize the series and the show. If the show is not recognized, I go back and double-check how the file is named.

Now that Plex recognizes the files, I can play them on any of my devices. The only issues I have had are the file sizes are too large or did not convert correctly. I’ve resolved these by using Handbrake to convert the .mkv file to a .mp4 file, using the “Android 720p” pre-set.

Posted in Uncategorized | Leave a comment

06/21/2017: Building my digital library (part 2)

No matter what the resolution, Plex is able to stream it to any device. The issue is that it is then on the device to figure out how to display the picture. This will often result in stretched or video with part cut-off. It also puts strain on the system to “figure out” how to display the video. So, I would rather just ensure that everything is set correctly at the file level.

Here’s a side note about converting to HD. As I mentioned, some of the shows were in resolutions lower than HD. If you remember, prior to HD, most TV screens (at least in the Unites States) were square. When HD came along, the picture was rectangular. So, any shows that were made prior to HD becoming widely used (around 2003) don’t fit properly on a HD television. Either they are stretched horizontally, zoomed in, or have “black bars” on each side of the picture. A few shows that my daughter likes are from the 90’s. Also, there are channels that broadcast in Standard Definition (SD). The shows are rectangular like HD, but the resolution is not as good.

The first thing I did is make a table of all the different resolutions of 16:9. The 16:9 is the screen ratio for HD. The 16 is the width multiple and the 9 is the height multiple. For example, a 1280×720 (720p HD) screen is a multiple of 80. If you multiply 80 by 16, you get 1280. If you multiply 80 by 9, you get 720.

Width (x16) Height (x9) Multiple
1280 720 80
1200 675 75
1120 630 70
960 540 60
800 450 50
640 360 40
592 333 37
576 324 36
560 315 35
544 306 34
528 297 33

Next, I would check the resolutions of the TV shows that were transferred. Each series usually had one or two different resolutions. For example, one series had a resolution of 528×300 in season 1, but changed to 528×332 in Season 4. Now that I knew that, I could compare the two numbers to match one of the 16:9 resolutions on the table I made. The 528×300 matched 544×306 resolution (16:9 x 34). The 528×332 matched 592×333 resolution (16:9 x 37).
I could just “force” them both into a set resolution, like 16:9 (35): 560×315. But, that would either mean cutting off part of the picture or adding a lot of the black box around the top or bottom of the picture. For example, the shows with the 528×332 resolution would have a total of 17 pixels cut off from the top & bottom. The shows with the 544×300 resolution would require an addition of 15 (total) pixels of black to the top and bottom of the screen.

Now that I know what my goal resolution is, I need to figure out how to get to it. So, I subtract and divide the different resolutions. Here is how I calculated the 528×300 resolution

Width Height .
544 306 Goal resolution
-528 -300 Current resolution
16 6 Difference
÷2 ÷2 Divided by 2
8 3 Padding

The “padding” is how many pixels of black lines I should add to top, bottom, left, & right of the picture to make it the goal resolution. As long as you have a set number of pixels to add that are divisible by 2 with a whole number (no fractions or decimals) remaining, things will be okay. In this case, I need to add 8 pixels of lines to the left and 8 pixels of lines to the right. The amount of pixels of lines I need to add to the top and bottom is an issue, though. The number “3” is not divisible by 2 into a whole number. So, we have to split it in a way that will equal 6, but will not be equal. I choose to put 4 pixels on the bottom and 2 pixels on the top (like the image below).
resolutions1

To be continued…

Posted in Uncategorized | Leave a comment