Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Creating Formula using check boxes | Excel Discussion (Misc queries) | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |