Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: How to multiply and add numbers in a cell

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
1138
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 28, 2023 04:22 PM

Hi Airtable Community,

Looking for some advice.

I have columns where I have cut length of webbings in a cell. I have noted the placement of the webbing, cut length and quantity in the cell. I wanted to have a column that can take the numerical data and divide by 36" (i.e. [(9.75 x 1)+(17x2)]/36)), to give me usage of the material. Is that possible without having to manually split each cut length into multiple columns? I do need the text description of the location (i.e. Haul loop) or else I cannot identify what the cut lengths are for, if I need a column for each description of cut length then I will have a lot of columns which I'd like to avoid.

Any suggestions would be appreciated!

Thanks,

Annie

3 Replies 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29, 2023 06:32 AM

The only way I can think of doing that is with a script I'm afraid

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29, 2023 05:59 PM

However, I highly recommend splitting out the information into multiple fields instead, then use a formula field to combine the information into the pretty looking text that you currently have. In fact, I would take that a step further, and recommend that you use linked records in a new table for this information - one linked record for each location. The other table would have several fields:

- the location

- the length

- the quantity

- a formula field that multiplies the length times the quantity to determine total inches

- a formula field that makes the pretty text for the one location (Haul loop 9.75 x 1)

- the backlink to this table

Then in your original table, have a two rollup fields. One rollup of the total inches for all pieces of webbing with the formula SUM(values). You can then divide the sum by 36 to get your yardage. The second rollup is for the text and would use the formula ARRAYJOIN(values, " ").

On advantage if this system is that you can have multiple pieces of webbing for each record, yet the number of fields stays the same, even if a single record ends up with several pieces of webbing. You also don't have to worry about fragile formula fields that might break if you don't type the information into the text field correctly.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 01, 2023 12:25 PM

Hi Kuovonne,

Thank you for this. This is very helpful.

I am interested in REGEX, I can consolidate the fractions into decimals. I am concerned if I create new table and fields it will bump me to paid plan if I add more rows. But how do I write a REGEX formula to extract the numbers and how do I write the sum formula to multiply and add all numbers within a column together? For regex, I have space between the parenthesis and between number and multiply sign (Haul loop 9 3/4 X 1) (Molle 17 x 2). The number of locations vary so there could be 2 places to multiply and add or 3 places to multiply and add, etc. (See print screen)

And for the splitting into fields and roll, is the attached print screen what you are recommending? And concatenate to combine all into one string in the main table and do the formula to add it all together? (See print screen)

Your suggestions are much appreciated!

Annie