+1Ok so airtable is great for certain things but it’s completely useless if we can’t create Sums such as =SUM(D2*E2+F2) if we ever want to export budget data into excel. All of my formulas are all gone if you try to open it in excel…just a bunch of numbers but with no more formula calculations in the columns if you want to add more data. Anyone know a trick?
Also it doesn’t work the opposite way either.
I don’t think there is a solution at the moment. As you know in Excel you’ll be giving a fomula to every individual cell and over here to a complete column (field). So I guess there won’t be any tricks. Sorry
I’m having similar issues, i’m trying to sum a complete column based on Category, something so easy in excel but here im struggling. something like:
No matter what I do I can get any result that makes sense, been on the forums read the documentation as best I could and zipo, lots of things work well and I am very impressed seemly simple tasks elude me.
+1I’m having similar issues, i’m trying to sum a complete column based on Category, something so easy in excel but here im struggling. something like:
No matter what I do I can get any result that makes sense, been on the forums read the documentation as best I could and zipo, lots of things work well and I am very impressed seemly simple tasks elude me.
Hey Peter
Airtable and Excel are actually very very different products. Airtable is visually presented like a spreadsheet utility because it’s easy to look at. That’s my understanding, anyway. Airtable and Excel are better at different things though, and position-based stuff is not one of the things Airtable is good at. Read the below infographic I made, it’ll make more sense than me giving you words alone.
Now, that all said, if you’d like an answer for your specific issue with regard to categories and stages and sums, if you can be very specific about the relationships between those fields and the data types they are, I’d be happy to give you a direct solution.
:slightly_smiling_face: :slightly_smiling_face: :slightly_smiling_face: :slightly_smiling_face:
Hey Peter
Airtable and Excel are actually very very different products. Airtable is visually presented like a spreadsheet utility because it’s easy to look at. That’s my understanding, anyway. Airtable and Excel are better at different things though, and position-based stuff is not one of the things Airtable is good at. Read the below infographic I made, it’ll make more sense than me giving you words alone.
Now, that all said, if you’d like an answer for your specific issue with regard to categories and stages and sums, if you can be very specific about the relationships between those fields and the data types they are, I’d be happy to give you a direct solution.
:slightly_smiling_face: :slightly_smiling_face: :slightly_smiling_face: :slightly_smiling_face:
Hello Thomas, Thanks for the heads up, yes I come to realise that this is not even remotely Excel like.
Pic with Formula below I hope will now make sense in what I am trying to do.
I understand that columns can only have one function.
A IF(Category = B" Main Stages",SUM(C Hours_Done,D Stage))
(Letters in bold relate to columns in the above pic)
Currently were using the Excel version that I made in Google Sheets which works very well, that been said I would like to take it to the next level, simply for the engineer on the ground as well as give programmatic feed back within sheet.
ToDo
+1Hello Thomas, Thanks for the heads up, yes I come to realise that this is not even remotely Excel like.
Pic with Formula below I hope will now make sense in what I am trying to do.
I understand that columns can only have one function.
A IF(Category = B" Main Stages",SUM(C Hours_Done,D Stage))
(Letters in bold relate to columns in the above pic)
Currently were using the Excel version that I made in Google Sheets which works very well, that been said I would like to take it to the next level, simply for the engineer on the ground as well as give programmatic feed back within sheet.
ToDo
I see. 3 things-
1- sum(Hours_Done, Stage) will not function. Like, at all. The reason why is because SUM is a numeric function, and Stage is not numeric.
2- What I assume you are trying to accomplish here is “If the category is Main Stages, then display the number of hours worked, and follow that with the value found in Stage.” I took the liberty of recreating a portion of your table schema to make this:
The formula in the Billing column is as follows:
IF(IF(Category=“Main Stages”, TRUE(),FALSE()),Hours_Done & " - " & Stage,"[Non-Main Stage]")
If anything in that formula doesn’t make sense to you, just let me know, and I’ll explain.
3- If you want to “take this to the next level” I would seriously look at form views, relational tables, and lookup tables to make an at-a-glance view along with forms that allow engineers on the ground to provide updates on the fly. This would require a fairly substantial rework of your base, but in the end you’d have a much more functional tool. A neat trick you can do with Airtable when you want to maintain control of the base (read: not give share access to anyone else) is to create a submission table (or multiple tables) along with forms to add records to that table, and have those submission tables update formula fields in tables that are not shared. I have used this to create web dashboards. After building the thing, simply create a view that displays exactly the information you want a dashboard element to display, create a sharing link of that view that you share with no one, and embed that view as an iframe anywhere you feel like it.
I see. 3 things-
1- sum(Hours_Done, Stage) will not function. Like, at all. The reason why is because SUM is a numeric function, and Stage is not numeric.
2- What I assume you are trying to accomplish here is “If the category is Main Stages, then display the number of hours worked, and follow that with the value found in Stage.” I took the liberty of recreating a portion of your table schema to make this:
The formula in the Billing column is as follows:
IF(IF(Category=“Main Stages”, TRUE(),FALSE()),Hours_Done & " - " & Stage,"[Non-Main Stage]")
If anything in that formula doesn’t make sense to you, just let me know, and I’ll explain.
3- If you want to “take this to the next level” I would seriously look at form views, relational tables, and lookup tables to make an at-a-glance view along with forms that allow engineers on the ground to provide updates on the fly. This would require a fairly substantial rework of your base, but in the end you’d have a much more functional tool. A neat trick you can do with Airtable when you want to maintain control of the base (read: not give share access to anyone else) is to create a submission table (or multiple tables) along with forms to add records to that table, and have those submission tables update formula fields in tables that are not shared. I have used this to create web dashboards. After building the thing, simply create a view that displays exactly the information you want a dashboard element to display, create a sharing link of that view that you share with no one, and embed that view as an iframe anywhere you feel like it.
Thanks for the effort, that line in itself was illuminating, however seems we may have crossed wires.
I was expecting the hour totaled for each stage to be presented much like below:
Not sure if this pic clarifies (a mock up) , I was looking for the numerical totalling of hours per stage to be shown, any advice is most welcome.
+1Thanks for the effort, that line in itself was illuminating, however seems we may have crossed wires.
I was expecting the hour totaled for each stage to be presented much like below:
Not sure if this pic clarifies (a mock up) , I was looking for the numerical totalling of hours per stage to be shown, any advice is most welcome.
Aha! I think I understand! So you are, in essence, using this sort of
like a GANTT chart, where you have stages and sub-stages, and you want the
formula for each stage to represent the sum of the hours in the substages
that comprise it, right? If that is correct, then this is where Airtable
really shines, in my opinion, however it will require you to separate this
data into multiple tables. Think about it like this- the records in this
table aren’t really all the same kind of item, though they are
related. When you set up a table, try to have every column mean
something to every record in the table. You have a ton of fields in this
table marked “N/A”. If there is a column that does not apply to an entire
category of records in a table, that’s a good indicator that that category
should be on a different table.
I can make an example base for you if you’d like, to show you what I’d do.
Aha! I think I understand! So you are, in essence, using this sort of
like a GANTT chart, where you have stages and sub-stages, and you want the
formula for each stage to represent the sum of the hours in the substages
that comprise it, right? If that is correct, then this is where Airtable
really shines, in my opinion, however it will require you to separate this
data into multiple tables. Think about it like this- the records in this
table aren’t really all the same kind of item, though they are
related. When you set up a table, try to have every column mean
something to every record in the table. You have a ton of fields in this
table marked “N/A”. If there is a column that does not apply to an entire
category of records in a table, that’s a good indicator that that category
should be on a different table.
I can make an example base for you if you’d like, to show you what I’d do.
Hello Thomas, Sorry for the delay in getting back to you and this, were in the middle of a project, people on holiday so my time has been used else where, would love a live example if possible when you can.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.