CODEWITHSUNDEEP
excelpivot-table
Maxim Srour
Maxim Srour

Reputation: 157

Excel Finding the highest decimal value per integer

I am trying to create a formula in Excel that returns the highest decimal value for every integer in a list.

For example, my sheet may have the values [10, 10.1,10.4, 11.3] and I need it to return both 10.4 and 11.3 since 10.4 is larger than 10 and 10.1, and 11.3 is the largest decimal value for 11.

I need this so that a pivot table can filter out the values as IDs and only return the highest value, but I can't get anything working to the point that providing my existing 'code' wouldn't help whatsoever.

Thanks for the help

Upvotes: 0

Views: 696

Answers (3)

Lambda
Lambda

Reputation: 1392

Pivot table and then group.

  1. pivot table

enter image description here

  1. change PivotTable Fields values to Max of values

enter image description here

  1. group

enter image description here enter image description here

  1. result

enter image description here

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If your list is sorted, you could use the Advanced Filter to generate a list of the highest decimal value for each integer.

Given:

enter image description here

Criteria Formulas:

A2: =A6<>INT(A6)
B2: =OFFSET(A6,1,0)=INT(OFFSET(A6,1,0))

Criteria Dialog Note that you can choose to have the results copied elsewhere

enter image description here

Results

enter image description here

Upvotes: 1

Hila DG
Hila DG

Reputation: 738

Solve as follows:

value   bracket decimal
10      =int(a2)    =a2-b2
10.4    =int(a3)    =a3-b3
10.1    =int(a4)    =a4-b4
11      =int(a5)    =a5-b5
11.3    =int(a6)    =a6-b6

or values:

value   bracket decimal
10      10      0
10.4    10      0.4
10.1    10      0.1
11      11      0
11.3    11      0.3

Now pivot by: bracket (columns/rows) and in the value section add the "original value", aggregate by "max".

Upvotes: 0

Related Questions