New Band Countries - how to log?
Posted: Fri Jan 26, 2024 12:46 pm
Many competitions, ladders, ask you to collect "New Band Countries". Some fine logging apps fall short.
To qualify, the contact must be a new DXCC entity on a new band, ie this entitity has never contacted on this band ever before. Only the current competition time period counts; this is the harder part.
Many logging apps will filter out contacts in a given time period, and even catalog them by band. However many to not search back from the beginning of the log, maybe many years, to exclude those heard before the competition period.
Fortunately there's an easy fix, with a spreadsheet. Any? Pretty much, as long as it can use a sheet for each year; the logic is the same. I'll give the M$ Excel code as well as the popular and free LibreOffice Calc.
Step 1.
Open a new Workbook. (Please look up what a "workbook" is before continuing.)
Open the first sheet and name it the starting year of the log. If the log is many years old, this first sheet maybe a composite for all the first years up to the last two or three.
Make a column left of every DXCC entity, and to the right a column for each band, say one each for 160m through 6m. Now there is a cell for each entity on each band, An entry in the cell means a contact. You can bet creative with different letters for different modes, confirmations etc. Maybe just KISS!
Step 2.
Make a new sheet for the current year. just like before, and enter the contacts on it from the cumulative log.
Step 3.
Make a new sheet for the current year but clearly named New Band countries.
Use a lookup and test formula to mark a cell if it is a New Band Country for this year.
Here's an example:
LibreOffice:
Relax. Let's see what it does, step at a time.
First we see if cell P39 in the Cumulative sheet for 2023 has a contact. It does -we enter a blank space, the first option -the contact isn't new, so we just put a space in the cell. Cell done.
If the contents of the cell in 2023 has no contact, but 2024 has a contact, it's new -so write a "1" in the cell. If 2024 doesn't have a contact, the cell gets a space.
So the New Band Country Sheet for 2024 gets a "1" in each cell where there is a New Band Country, but never if the contact has been made before.
The sharp-eyed will notice that what's going on here is testing of a cell in a table and writing another table depending on the test. Yep, we're using a spreadsheet as a database, and this operation would be done better with SQL. The only excuse is that more people know how to drive a spreadsheet than a database.
For fun see if you can write the SQL equivalent of the formula above!
To qualify, the contact must be a new DXCC entity on a new band, ie this entitity has never contacted on this band ever before. Only the current competition time period counts; this is the harder part.
Many logging apps will filter out contacts in a given time period, and even catalog them by band. However many to not search back from the beginning of the log, maybe many years, to exclude those heard before the competition period.
Fortunately there's an easy fix, with a spreadsheet. Any? Pretty much, as long as it can use a sheet for each year; the logic is the same. I'll give the M$ Excel code as well as the popular and free LibreOffice Calc.
Step 1.
Open a new Workbook. (Please look up what a "workbook" is before continuing.)
Open the first sheet and name it the starting year of the log. If the log is many years old, this first sheet maybe a composite for all the first years up to the last two or three.
Make a column left of every DXCC entity, and to the right a column for each band, say one each for 160m through 6m. Now there is a cell for each entity on each band, An entry in the cell means a contact. You can bet creative with different letters for different modes, confirmations etc. Maybe just KISS!
Step 2.
Make a new sheet for the current year. just like before, and enter the contacts on it from the cumulative log.
Step 3.
Make a new sheet for the current year but clearly named New Band countries.
Use a lookup and test formula to mark a cell if it is a New Band Country for this year.
Here's an example:
LibreOffice:
Code: Select all
=IF(ISNUMBER($'2023'.P39)," ",IF(ISNUMBER($'2024'.P39),1," "))First we see if cell P39 in the Cumulative sheet for 2023 has a contact. It does -we enter a blank space, the first option -the contact isn't new, so we just put a space in the cell. Cell done.
If the contents of the cell in 2023 has no contact, but 2024 has a contact, it's new -so write a "1" in the cell. If 2024 doesn't have a contact, the cell gets a space.
So the New Band Country Sheet for 2024 gets a "1" in each cell where there is a New Band Country, but never if the contact has been made before.
The sharp-eyed will notice that what's going on here is testing of a cell in a table and writing another table depending on the test. Yep, we're using a spreadsheet as a database, and this operation would be done better with SQL. The only excuse is that more people know how to drive a spreadsheet than a database.
For fun see if you can write the SQL equivalent of the formula above!