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.
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.
Why would you need a formula? It's obvious by just looking at the list.
The OP needs a formula to input into a spreadsheet.
I would like to highlight it.Quote:
Why would you need a formula? It's obvious by just looking at the list
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.
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.
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))
Thanks JB... that works great.
All times are GMT -7. The time now is 10:21 AM. |