Skip to main content

How to get the highest value out of a column

  • August 31, 2018
  • 1 reply
  • 92 views
D

Hi Guys.

Here’s my question, quite simple actually… like the title says, i am trying to get the highest value inserted in a column, I have tried the MAX() function, but it doesn’t give me the highest, it just gives me the same number from the column im using. For example: I have a column named: “number of quotes” and each row has a random number, the highest being 200. in another column named “highest quote number” I wanted to get that number(200) from “number of quotes” and apply to all other rows…since im trying to use a formula in witch one of the params is the highest current number in the “number of quotes” column.

I hope I made myself clear, here’s the formula im trying to use, if it helps:

new_value = ( (old_value - old_min) / (old_max - old_min) ) * (new_max - new_min) + new_min

    1 reply

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • September 1, 2018

    The spreadsheet metaphor for Airtable is an imperfect one — and I’m going to make it even more imperfect. If you think of a spreadsheet as supporting calculations addressable in two dimensions — row and column — then Airtable supports only one-dimensional calculations: that is, only within a row. (Of course, Airtable offers functionality spreadsheets lack.) There is no way to address directly a cell in a given row of a table in a calculation performed within a cell from a different row.

    However, this is a way to do so indirectly. Elsewhere in the forum you’ll find a long post and example bases I wrote explaining how to perform such multi-record or cross-record calculations. Fortunately for you, your application doesn’t require as complex a solution as that post describes — but you will need a few of its components:

    1. First, every record in your table needs to be linked to the single record in a second table. I’ve provided [possibly too] detailed instructions on how to do this, but here’s a quick-and-dirty breakdown of the steps:
      1. Create a linked-record field in your main table that links to a table called [Calc]. (Create a new field; select ‘Link to another record’ as the field type; select ‘Create a new table’; name the new table.)
      2. Somewhere — in a text field in the base; in Notepad; from a web page; anywhere — mark and copy a period character (that is, '.', not someone dressed as in a Dickens’ novel).
      3. Left-click on the header for your newly created linked-record field, which will select every cell in the column.
      4. Press Ctrl-V to paste the copied character into each cell of the column. This will create a new record in [Calc] with the {Name} of '.' and link it to every record in your main table.
    2. In [Calc], create a rollup field called {MaxQuotes}. It should follow the link from [Calc] to your main table and roll up {Number of Quotes} using the aggregation function MAX(values).
    3. In your main table, create a lookup field. This one should be called {HighestQuoteNumber}; it should follow the link to [Calc] and look up {MaxQuotes}.

    That’s it. Keep in mind this will work for all records currently in your main table; any records later added to [Main] will need to be linked to the '.' record in {Calc}. This can be done manually by clicking on the plus sign ('+') in the linked record field, by copy-and-pasting a ',' into the new record’s linked-record field, or by clicking on the fill handle (the small '+' in a cell’s lower-right corner) of the previous row in the linked-record column and dragging it into the newly-added record.

      Terms & ConditionsAccessibility statement