At this point, we're going to make what I call the "index table." Basically, we're just going to divide the distances we've generated into the references we've generated.
I suggest making a table that looks like this:
All we want to do now is insert our distance calculations from the previous post into the above table.
We'll have to make a reduced version of the distance post's last table, by keeping only the part highlighted in orange, below. Note below how the cell K3 equals the cell C3 - but that the reduced version has no merged cells:
I would normally be doing this on several worksheets in one document, to save space, but here I'm arranging the format just to make it clearer to the reader. Honest, it is best to set up one worksheet per level of table in your document - even several documents, as I keep my distance calculations completely separate from my index calculations, just for my sanity.
Very well, I'll remove the little table on the left of Table C and we'll move onto the next part, which is slightly tricky. Each number, from cell D4 to cell AI12 needs to be divided by the distance numbers. The way we do this is to divide D4 by C4, D5 by C4, D6 by C4 and so on, the same way for every line.
We do this by creating another complete table, a near duplicate of Table C:
We'll call these the "top half" and the "bottom half." D16 in the bottom half equals D4 in the top half divided by C4. By putting "$" signs in front of the C and the 4, we ensure that when we copy left from D16, all the cells above will be divided by the same distances we've inserted.
I hope that is clear. The benefit here is that if we want to put in the distance numbers for a different market (other than Marzarbol, which is shown here), then the "TOTALS:" will change automatically without our needing to build this table again.
These totals are the "Index" for the pricing table. I can show the reader how to build further content from the Index, but obviously I'm interested in getting readers to donate $10 to my Patreon, to have a look at my own pricing table from start to finish. Please excuse the pitch, but I'm not quite earning enough money to see me through to managing my bills before the first of May and I can use all the help I can get (either through Patreon or through direct donations). We had a good donation yesterday and it lifted everyone's spirits, given that we're spending a lot of time counting every penny at the moment.