Re: Formula Help. Adding Rows / Filled Amounts
Hello, I am new to this software but have written a pretty functional Airtable to keep track of the UK 50p coins I am collecting.
I’m able to add costs and dates and photos and descriptions and have even been able to add columns up at the bottom using the Filled option. I have three columns (16, 33, 7) which I would now like to add together to give a total of coins. I would then like to multiply that number by .50 to give me the bare minimum value of my collection.
I think it ought to be simple but I am held back by lack of knowledge of basic formulas and would really appreciate any help the community could give me.
Hi Ben! Thank you for looking at this with me!
Yes, each record is an individual version of a (UK) 50 pence piece coin (50p). There are 70-80 different designs available at the moment and I have a record for each design with three columns for possible duplicates I may own.
‘1st’ can only be an uncirculated version so if I don’t own an uncirculated one but do own a circulated one that would go into ‘2nd’ and any more would go into ‘3rd.’ If I own another one after that I am not going to bother recording it! Potentially I could own three of one design, or none. The condition is recorded from a drop down.
Those 16, 33, 7 numbers are the results of counting fields which aren’t empty - doesn’t matter what it says in the field - an entry means I have that coin.
I want to keep an account of how much I have spent so far - that one must have been easy enough as I have the total in place :grinning_face_with_big_eyes: (£65.88) But I also want to know what the face value of my collection is worth. As they are 50p piece coins they are actually only worth 50p in a shop so if I have 56 coins they are worth £28. I’d like a running total for this and can’t work it out. If this was Excel I would simply add a formula in a random field and add those three amounts together. . .
I have hidden a lot of fields to make this look screenshot to look simple. I love the flexibility all these fields give me when I have them all in place!
I’d also like to say that I am loving Airtable so far. I used to use Bento on my Mac and am still in mourning for it. I searched for a long time to find Airtable!
Thank you for reading this far!
Okay!
After I typed this I realized how dense it can be… so please let me know if you need more guidance on how this works/how to replicate it.
So, there are a few things about your base design that are making your life a bit hard.
You should look to split your base into two tables.
One table is for the coin designs.
The second table is for your coins.
This allows you to link the coins in your collection to their designs.
In your original table, you have about a ton of fields for those three potential coins.
You indicated that if you had four or more coins, you would just have to settle for not being able to record it.
With a few tweaks and adjustments, you can keep track of every coin you own!
Here’s a base I’ve made for you to look at.
Here’s How It Works:
You can see in this quick chart, that we have six total designs, as well as six coins.
However, despite having six coins, we only have coins from three designs.
This means that we can easily identify exactly what’s in our collection, and we can identify which design they are from.
This allows us to do two things:
-
We can collect information about the design:
These are things like the year of the coin, the front/back design, other notes about the coin overall, etc. -
We avoid data congestion/bloat.
Instead of having to cram all of that information about our collection and the coin design itself into a single record, we can separate them into really clean and efficient records.
Okay okay… but what about my original question?
One of the huge advantages of database tools like Airtable, is that we can summarize the relationships between all of our data in a very easy and straightforward way.
If you open up the shared base I linked, you’ll see two tables that I mentioned above.
The Designs table, and the Coins table.
(I just added in some filler records, you can copy the base into your own workspace and then add in your records as necessary.)
In the designs, I have a record for each coin design, as well as the year of the design, as well as attachment fields for you to attach your photos of the designs.
In the next table, Coins, you’ll find an individual record for every individual coin that you own. It doesn’t matter if you have multiple coins for a single design, each coin gets a record.
In the example base I shared, I created some example fields, such as the condition, the date you got the coin, and the price you paid for it.
Now, you’ll see the linked record field that allows you to link the coin design, to the coin you own.
The primary field on the record is a formula field that takes all of that information and puts it into a pretty format. If you wanna nerd on it, here’s the actual quick formula I wrote for it:
IF(
{Design}, {Design} & " Coin", "Missing Design"
) &
"\n" &
IF(
{Date Acquired},
"Purchased " & DATETIME_FORMAT({Date Acquired},'l'),
"Missing Purchase Date"
) &
"\n" &
IF(
{Purchase Price},
"For: £" & {Purchase Price},
"Missing Purchase Price"
)
If any of the fields are empty, then it will just indicate that it’s missing one of those fields.
Since you have each coin linked to their respective designs, you can take the coins you own, and summarize the data in the designs table.
(As I’m typing this out, I realize that this is probably hard to really conceptualize. So please let me know if you want me to break it down in a more granular way.)
Now, back in the Designs table, you’ll notice the Total Owned and the Total Cost fields.
The Total Owned field is a count field that counts the number of linked records from the Coins table.
The Total Cost field is a rollup field that takes all of the values of a field and allows you to manipulate and display values in different ways.
In this case, I have the rollup field take all of the {Purchase Price} values from the Coins, and adds them all together. I then take that value and put it into a hidden formula field called Minimum Value.
From there, I use the Summary app to put together the sum of all of the minimum prices.
This leaves us with the total minimum value of your entire collection!
Seriously though… let me know if none of this makes sense or if you want more of an in-depth on how it all works.
@Susan_Collins - In order to copy the base, all you have to do is hop into the base I linked above and click the Copy Base option at the top right!
Then it will copy the base to your own workspace where you can then interact and put in your own data!
