dannac Posts: 267, Reputation: 9 Full Member #1 Jun 27, 2014, 02:10 PM
Find lowest after decimal... closest to whole number.
2.45
3.75
2.08
4.06

Need formula that would choose 4.06 from above list.
Even though it is the largest number, the .06 is closest to the whole number.
 Wondergirl Posts: 37,870, Reputation: 5429 Jobs & Parenting Expert #2 Jun 27, 2014, 02:38 PM
Why would you need a formula? It's obvious by just looking at the list.
 J_9 Posts: 40,286, Reputation: 5645 Expert #3 Jun 27, 2014, 02:47 PM
The OP needs a formula to input into a spreadsheet.
 dannac Posts: 267, Reputation: 9 Full Member #4 Jun 27, 2014, 04:50 PM
Why would you need a formula? It's obvious by just looking at the list
I would like to highlight it.
 dannac Posts: 267, Reputation: 9 Full Member #5 Jun 28, 2014, 04:00 PM
Got it with the use of helper columns.

Used MOD function to separate the whole number.
Used MIN function to find the lowest.
Then conditional format to highlight.

If someone has a solution without helper columns, I'm all ears.
 JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #6 Jun 30, 2014, 03:49 PM
Try this array formula:

=INDEX(A1:A4,MATCH(ROUND(F1,2),ROUND(MOD(A1:A4,1), 2),0))

... confirmed by pressing CTRL+SHIFT+ENTER, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula.
 JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #7 Jun 30, 2014, 05:10 PM
My bad, I had a helper cell in use while constructing that formula. The final Array formula should be:

=INDEX(A1:A4,MATCH(ROUND(MIN(MOD(A1:A4, 1)),2),ROUND(MOD(A1:A4,1),2),0))
 dannac Posts: 267, Reputation: 9 Full Member #8 Jul 1, 2014, 08:46 AM
Thanks JB... that works great.

 Question Tools Search this Question Search this Question: Advanced Search