CODEWITHSUNDEEP
excel-formulagoogle-sheets
Joseph
Joseph

Reputation: 1744

Spreadsheet/Excel array functions that compare and validate values

I got this dataset

ID  fruit   price
1   apple   10
2   apple   50
3   apple   100
4   banana  10
5   banana  20
6   banana  50

and would like a (set of) forumla(s) that go through the rows and output the row for each fruit that has the highest price.

In e.g. PHP I would do something like this

foreach $array as $row{
  if in_array( $row[fruit] ){
    /* check if current $row[price] for current $row[fruit] is larger than existing post. If yes replace */
  }
}

How would I do that in Google Spreadsheets / Excel?

Upvotes: 1

Views: 184

Answers (3)

pnuts
pnuts

Reputation: 59485

A pivot table with fruit for Rows and price for Values (Summarise by: MAX) may serve.

Upvotes: 0

Tom
Tom

Reputation: 56

I've put together a quick VBA macro that you could use in excel that will output the fruit with the highest price.

The macro converts the table of fruit toa an array and then loops through the array to find the fruit with the highest value, before outputting it to the sheet. This macro relies on the table of fruit being positioned in columns A to C.

Sub getMaxPriceFruit()

'put data table into an array
Dim dataTableArray() As Variant
dataTableArray = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)

'loop through the aray looking for the largest value
'capture array index in variable when largest is found
Dim maxArray(1 To 1, 1 To 3) As Variant
maxArray(1, 1) = 0
maxArray(1, 2) = ""
maxArray(1, 3) = 0

Dim i As Long
For i = 1 To UBound(dataTableArray)

    If dataTableArray(i, 3) > maxArray(1, 3) Then
        maxArray(1, 1) = dataTableArray(i, 1)
        maxArray(1, 2) = dataTableArray(i, 2)
        maxArray(1, 3) = dataTableArray(i, 3)

    End If

Next i


'output the fruit with the max value
Range("F2").Value = maxArray(1, 1)
Range("G2").Value = maxArray(1, 2)
Range("H2").Value = maxArray(1, 3)


End Sub

The limitation of this script is that if there are two fruit with an equal max value, the first fruit in the list with that value will be selected as the winner. If you would like the additional code to output multiple fruits if they have the same max value I can provide, but put simply you could utilise the maxArray array to capture all of the top ranking fruits and then loop through this array to output them all in one go.

Hope that helps!

Upvotes: 0

Captain
Captain

Reputation: 2218

You can do it in Excel with array formulas (so you enter it with Ctrl+Shift+Enter)...
If your fruit is in B and price in C your array formula in D2 would be

=C2=MAX(IF($B$2:$B$7=B2,$C$2:$C$7,0))

This will give you TRUE or FALSE for whether that row has the highest price for that fruit.

It works by doing an IF on the array of fruits (rows 2 to 7 - you can make it longer) being the same as the current fruit - if it is the same, return the price, otherwise 0. We then get the MAX and compare it to the current row's price.

Good luck!

Upvotes: 1

Related Questions