Ask Experts Questions for FREE Help !
Ask
    dannac's Avatar
    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's Avatar
    Wondergirl Posts: 37,846, Reputation: 5428
    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's Avatar
    J_9 Posts: 40,277, Reputation: 5644
    Expert
     
    #3

    Jun 27, 2014, 02:47 PM
    The OP needs a formula to input into a spreadsheet.
    dannac's Avatar
    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's Avatar
    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's Avatar
    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's Avatar
    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's Avatar
    dannac Posts: 267, Reputation: 9
    Full Member
     
    #8

    Jul 1, 2014, 08:46 AM
    Thanks JB... that works great.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to find lowest pH which is still completely soluble? [ 0 Answers ]

The intrinsic solubility of a drug, sulfathiazole, is ~0.002 M and its pKa is 7.12. What are the lowest pHs at which a 1% and 5% solution of Na sulfathiazole (mol wt = 304) are completely soluble? I tried calculating the molarity of each and subtracting it from pkA. The formula I used was...

How can I convert decimal number to degree and time [ 1 Answers ]

How can I convert decimal number to degree and time

How to get only the highest and the lowest number as output in a set of data [ 4 Answers ]

Dear sir, I would like to know how to get the highest and the lowest ages as answer in a set of data with many range of ages.the output should be with the name place and age of the person with the highest and the lowest age.kindly let me me know the answer.


View more questions Search