A year into the new normal with Covid-19 and working from home, sadly the body has taken a toll in terms of weight gain. The one good thing was that it wasn’t just me or my wife but it was a group of us that had increased weights, and instead of calling it Covid-19, we started calling it Covid-25, where the 25 stood for the amount of weight that we increased.

So of course my genius wife had this incredible idea that we should probably have a competition. A group of us would put $50 each, and at the end of 4 months whoever lost the most weight would get the money. To keep this fair and keep the transparency between all of us, I created a Google sheets that we could log all our weights in. The sheet was simple to begin with each column representing one person and a date so that we could log each time we weigh ourselves in the appropriate cell.

Fro my previous weight loss experience, we learned that it’s not the weight loss that matters but it was the percentage of body weight loss that mattered, as all of us had a different starting point and it would be unfair if we counted the weight instead of percentage. So, I created another sheet whose sole purpose was to figure out the weight delta and percentage fat loss.

The business analyst side of me started seeing some of the requirements coming in:

  • ability for all of us to enter our weights,
  • ability to calculate the possible percentage weight loss from initial entry,
  • the ability to see who is leading.

This was a good list to start with so creating the percentage weight loss was easy. All I needed to do was check what the current rate is figure out what the initial weight was and come up with a difference. Then we needed to figure out how to keep track of who the winner is. Lucky for us, we knew that this is going to run for 4 months. At the end, whoever had the most percentage weight loss would be the winner. I did that by copying the following formula all the way down to the end of the competition.

The table on the right is unsorted, while the left is organized by rank

I created a small table that would tell us who lost what and then sorted them so that we can keep a track off who the the leader is. I added a header cell that told me who the leader was, so quickly looked at the vlookup / Index / Match functions in Google spreadsheets and apply that so now whoever had the most weight loss would automatically be shown as leader in the header title.

Value of R1 =INDEX(Sheet3!A1:M1,MATCH(MAX(Sheet3!A123:M123),Sheet3!A123:M123,0))

Now the challenge still remained about entering the weight daily. For this, I used one of my old integrations and said every time I weigh myself it will update a cell in my Google sheets. The trick was to figure out which cell to fill out, so using the existing start date and finding out the current date I created a Delta which would then update the appropriate cell in the sheet.

High level IFTTT Routine
Filter code used, to figure out which cell to populate. I was Column B while Elma was Column C

Now if you are interested in doing your own, use the following copy and reach out if you have any questions. https://docs.google.com/spreadsheets/d/1t9yRui4Xgz57qvhx0XwmHPTppfs_M04rjZI2ocs8h9Q/edit?usp=sharing