General Betting
There is currently 1 person viewing this thread.
Can any1 help me with the following please:
In column B , I have a list of figures which change when my I update other sheets in the workbook
In Column A , the figures never change but the values in column b beside them always do. In cell H4 , I want it to read the nearest figure to 100 from the list in column B and pick the corresponding value in Column A.
Does that make sense and if it does can Excel do this?
Many Thanks for any help
In column B , I have a list of figures which change when my I update other sheets in the workbook
In Column A , the figures never change but the values in column b beside them always do. In cell H4 , I want it to read the nearest figure to 100 from the list in column B and pick the corresponding value in Column A.
Does that make sense and if it does can Excel do this?
Many Thanks for any help
sort by:
Show
per page
Replies: 18
|
By:
=VLOOKUP(100,a1:b10000,1,TRUE)
try that.. |
|
By:
I don't think that will be easy to do. You can use Match and Index to to a left look-up but I don't know how you'd find the closest to 100 rather than exactly 100. Presumably column B can't be sorted ?
|
|
By:
thanks starfish that is what i have but it returns n/a
to Ian sometimes the figure doesn't fall exactly on 100 which is the problem |
|
By:
I think there's an easy way to do this, but I don't know it.
Can you do the following though: In column C, have =abs(100-b1) Then in column D use rank to return the figure from the vlookup. something like =if(rank c1, c1:c100)=100,vlookup (blah),"irrelevant") |
|
By:
i thought you must have tried that.
Sometimes excel reads the cell formula rather than the value, do you think thats the problem here? does adding a column witb =VALUE(B1) work? it should do but it doesnt always work for me |
|
By:
Do as Lori suggests ..
In Col C insert this formula =ABS(100-B1) In Col D insert this formula =RANK(C1,$C$1:$C$100,1) Then use this formula anywhere =INDEX($A$1:$D$100,MATCH(1,$D$1:$D$100,0),1) |
|
By:
Thanks guys for all your help and it is sort of working but I am just getting the column header in cell H4 , instead of the a value
|
|
By:
What happens if you delete the column headings row ?
|
|
By:
I now get the answer 1 Ian, which is the top cell now
|
|
By:
just out of interest, is it likely there will be two values in colB with the same value and are all values returned in colB less than 100?
It could get a bit more complicated if you get 99, 99, 101 etc are the values in colB always whole numbers or are you getting for example 91.333333333etc? |
|
By:
no jollop there is no duplicate values and the figures do go up to the 100,000s of thousands and yes though there is figures with two decimal places 2300.44 etc
|
|
By:
so you could return two values both as close to 100 ie 90 & 110 ? which one would you want to match with column A?
|
|
By:
it would then be the higher one Jollop
|
|
By:
In C1 (this is an array formula so press control&shift&enter not just enter)
=INDEX($B1:$B1000,MATCH(MIN(ABS($B1:$B$1000-100)),ABS($B$1:$B$1000-100),0),1) In H8 (or whichever cell you wanted the result in) =OFFSET($A$1:$B$1000,MATCH($C$1,OFFSET($A$1:$B$1000,0,1,ROWS($A$1:$B$1000),1),0)-1,0,1,1) Try that it might need adjusting slightly. |
|
By:
no Jollop that ain't working mate, don't worry I will figure something out. Many thanks to you and everyone for your help!!!!
|
|
By:
IN H4 enter this =MATCH(H2,dat,0)
Select all data in column B. In the name box type dat then enter In cell H1 type 100 In the name box type tar then enter In cell H2 paste this formula and enter it as an array to do this press shift and control and enter. =INDEX(dat,MATCH(SMALL(ABS(tar-dat),1),ABS(tar-dat),0)) |
|
By:
If you are not familiar with excel
the name box is the left most box in the formula bar if you select Cell H1 you will see in the name box H1 In that box type as per previous instructions. When entering an array formula, When you have pasted or typed in the formula, the insertion must be blinking in the formula bar, then whilst holding down shift and control press enter. This will put curly brackets around the formula. Your answer that you wanted will be in H4 as you requested |
|
By:
An interesting aside is that if you had 99 in column B and 101 in
column B. The closest number to 100 is actually 101 in % terms than what 99 is. So the formula in H2 would give you 101 even though the difference in both cases is only 1. |