Hello all. I have been using AirTable for the last two years as a way of managing around 100 hard drives for the post-production company I work for. For the last year we have started to use a cloud system, however, this system has a set amount of storage that we pay for ‘working projects’ and the plan with this system is that after a certain amount of time it gets moved to a ‘glacier storage’ on the cloud which costs less to us. There is also a fee to move that a project that is in ‘glacier storage’ back to the ‘working project’ storage. All of this will need to be pasted back to our clients. So I need to have a database where I can input each project storage size and the database then calculates how much the cloud storage will be over the year and also how much it will cost to retrieve these items.
So far my database consists of various identification cells (project name, client, etc) as well as couple of formula columns that work out what the total storage of each project is - these take the total bytes of the project and convert them into KB, MB, GB, TB automatically. I got the formula for this from this post here.
Now for the tricky part. I will try and break down my ask into two parts. I have a set price from the ‘glacier storage’ which is £5.00 per TB per 12 months. I want to be able to create a way whereby when I input the total storage of a project I get an instant cost estimate for how much it will cost to move and store that project in ‘glacier storage’. For example if Project A is 100 GB in total, the cost should £500, etc. However, and where this gets a bit more tricky, the projects are all various sizes. As I mentioned earlier I use a formula to summarise the size of each project into TB, GB, MB or KB. So where I am struggling is, how do I create a formula that can assess the different sizes of the projects, then convert them into GB to reach a cost? One idea I did have was to have a formula which creates the cost based on the total bytes size (which AirTable does) and then in the final cost tab have formula that then converts that figure back to a ‘per TB’ figure.
The second part of my ask is a similar type of thing where I need a cost of moving a project back to the ‘working project’ storage which again has a fixed price of £0.05 per GB and I am assuming that the formula for the above would for this but I would need a conversion for into GB?
Anyways, I hope that makes sense to someone and any help would be awesome.
Cheers
