Hi
in the column A I have some numbers eg. 3,3.25,3.25,3.5,4,4,4,4.25,4.25,4.5,4.75,4.75,4.75,5
I want to use B1 as input , where user can input any number from Column A
Now I want that in B2 the result will be as the next higher number from Column A
eg.
1. If B1 = 3 then B2=3.25
2. If B1=3.25 then B2=3.5
3. If B1=4 then B2=4.25
I is possible without macro ?? if possible then plz help me
Try this...
=IFERROR(INDEX(A1:A14,MATCH(B1,A1:A14,0)+1),"")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
no, its not working. If I give 3 in B1 then its working. But if put 3.25 it is giving 3.25
Ooops!
I just noticed there were duplicate numbers.
Data Range
Try this array formula**:
=IFERROR(1/(1/MIN(IF(A1:A14>B1,A1:A14))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
excellent, thnx
But can u give me explanation plzI will be glad to u
=IFERROR(1/(1/MIN(IF(A1:A14>B1,A1:A14))),"")
If there are no numbers greater than the lookup value then the MIN function will return 0.
You don't want a result of 0 so I intentionally wrote the formula so that it will cause an error to be generated if there are no numbers greater than the lookup value. But, we then use the IFERROR function to trap that error.
We can say that 1 divided by 1 divided by some number will equal that number UNLESS that number is 0. When the number is 0 the division operations will result in the #DIV/0! error.
For example:
If the result of:
MIN(IF(A1:A14>B1,A1:A14)) = 3.25
Then:
1/(1/MIN(IF(A1:A14>B1,A1:A14))) = 3.25
Then:
=IFERROR(3.25,"") = 3.25
However, if there are no numbers greater than the lookup value the result of:
MIN(IF(A1:A14>B1,A1:A14)) = 0
Then:
1/(1/MIN(IF(A1:A14>B1,A1:A14))) = #DIV/0!
Then:
=IFERROR(#DIV/0!,"") = "" (blank)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks