This post is part of a series investigating Mr. Sederquist’s Form Fight System. This series will also describe some changes I have made to fit the system into my class structure.

Mr. Sederquist’s Original Form Fight Boss Display Sheet

The Boss Display Sheet on Mr. Sederquist’s Form Fights is a masterpiece of spreadsheet formula design. In my previous post (Mr. Sederquist’s Form Fights) I brokedown how all of these formulas interact. In this post I will explain how I have added “Battle Damage” to the Display Sheet.

VLOOKUP Chart

The Battle Damage system utilizes the VLookup Function.

This formula =if(A5=””,””, vlookup(A5,Formulas!$K$2:M12,2)) is actually a mash up of an If/Then formula and the VLookup. The If/Then portion is <=if(A5=””,””, ___)>. This is telling the Google Sheet that IF cell A5 equals any number (“”,”” means any number) then do what comes after the second comma – in this case begin the vlookup formula. The vlookup is <vlookup(A5,Formulas!$K$2:M12,2)>. This formula is telling the Google Sheet to use the number in cell A5 to lookup information from the table found from K2 to M12 on the Formulas Tab. The “2” at the end of the formula is telling Sheets to return the information in the 2nd column of the table. This is what it looks like.

In the original picture the “Boss” damage is full health with 35 HP in cell A. The formula will examine column K, find the row that 35 fits in, and then return the information in the 2nd column. So the formula will display the image in L12. Now look what happens when the HP goes down to 34.

Don’t worry. This is not bloody and gory. The next battle damage reveals a Terminator machine!

Now that Cell A5 = 34 the formula looks up that number in column K, finds that 34 is in between K11 (31) and K12(35), and returns the information in cell L11.

Creating the Vlookup Chart

For a more detailed description check out the post VLookup: Creating Battle Damage. The simple explanation is that Column K represents the Hit Point “Levels”. Change the numbers in this column to if you change the original starting HP for the boss (there are ways to automate this, but that’s a different post). Column M contains published Google Draw Image URLs that slowly show the Boss taking on Battle Damage. The 1st picture in the series should be the bottom most URL link and the final picture in the series is the topmost. Column L (the middle column) uses the function =image to display the Google Draw found at the URL. For Example, =image(M12) will display the image found at the URL address in cell M12.

Battle Damage

As students decrease the Boss’ HP the vlookup will automatically lookup the new HP number. If set up correctly the new images being displayed will make it look like the boss is slowly taking damage from the students’ attacks. Battle Damage is a nice visual way of demonstrating the students’ success.