Re: Airtable & Excel NOT "SUM Formula" compatible?!
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
- Display ‘Main Stages’ cumulated hours in separate column filtered by ‘Stage’ (this request)
- Once a task is complete change ‘dropdown cell content’ from current state to ‘done’
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.
