Bother with a formula
 

Hi all,

I need a little help with a formula - if it can be done ...
Below is a table that shows kw units and cost. A calculation is
performed on information user supplies and the result can be anywhere
between 0.0 and 3.
If for instance the result returns 1.3, can I get a formula to check is
1.3 is there and if not select the next nearest 1 higher ie 1.5


A B
0.5 51
0.75 51
1 62
1.25 62
1.5 63
2 63
2.25 63
2.5 64
3 64


--
PeterG
------------------------------------------------------------------------
PeterG's Profile: http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=278328

Hi
do you want the value from column a or B returned?
for volumn A:
=VLOOKUP(1.3,A1:B20,1,TRUE)

for column B:
=VLOOKUP(1.3,A1:B20,2,TRUE)

Assumption: your data is sorted ascending in column A


--
Regards
Frank Kabel
Frankfurt, Germany


PeterG wrote:
Hi all,

I need a little help with a formula - if it can be done ...
Below is a table that shows kw units and cost. A calculation is
performed on information user supplies and the result can be anywhere
between 0.0 and 3.
If for instance the result returns 1.3, can I get a formula to check
is
1.3 is there and if not select the next nearest 1 higher ie 1.5


A B
0.5 51
0.75 51
1 62
1.25 62
1.5 63
2 63
2.25 63
2.5 64
3 64

try this where q2 is your formula
=INDEX(P1:P100,MATCH(SMALL(O1:O100,COUNTIF(O1:O100 ,"<"&Q2)+1),O1:O100,0))

--
Don Guillett
SalesAid Software

"PeterG" wrote in message
...

Hi all,

I need a little help with a formula - if it can be done ...
Below is a table that shows kw units and cost. A calculation is
performed on information user supplies and the result can be anywhere
between 0.0 and 3.
If for instance the result returns 1.3, can I get a formula to check is
1.3 is there and if not select the next nearest 1 higher ie 1.5


A B
0.5 51
0.75 51
1 62
1.25 62
1.5 63
2 63
2.25 63
2.5 64
3 64


--
PeterG
------------------------------------------------------------------------
PeterG's Profile:
http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=278328

How about this:
It will round the value in C1 up to the nearest multiple of 0.25 then
lookup that value

=VLOOKUP(CEILING(C1,0.25),A1:B9,2)


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=278328