On Sun, 2006-02-26 at 19:55 -0500, Jack Gates wrote: > I am trying to figure out how to create a formula that is probably complex. > I > do not know what function to use and I do not understand much of the help > documentation that I have read in OOo 1.1.3 > > I want a cell (A2) to read the value of another cell (A1) and then put a > value > in cell A2 after it has determined what group of value ranges that the value > in cell A1 belongs to. > > Example: > > A1 >= 0.01 but <= 20.00 then A2 = 5.75 > A1 >= 20.01 but <= 40.00 then A2 = 6.50 > A1 >= 40.01 but <= 80.00 then A2 = 8.50 > A1 >= 80.01 but <= 120.00 then A2 = 10.50 > A1 >= 120.01 But <= 160.00 then A2 = 11.95 > A1 >= 160.01 But <= 200.00 then A2 = 12.95 > A1 >= 200.01 But <= 300.00 then A2 = 15.95 > A1 >= 300.01 But <= 400.00 then A2 = 17.95 > A1 >= 400.01 But <= 500.00 then A2 = 19.95 > A1 >= 500.01 But <= 749.99 then A2 = 21.95 > A1 >= 750.00 then A2 = 0.00
You need a lookup function (see LOOKUP). Arrange your values as follows (say): D1 E1 0.00 0.00 0.01 5.75 20.01 6.50 40.01 8.50 80.01 10.50 120.01 11.95 160.01 12.95 200.01 15.95 300.01 17.95 400.01 19.95 500.01 21.95 750.00 0.00 Then cell A2 =LOOKUP(A1;D1:12;E1:E12) The first column (D) are threshold values. If you need to round or truncate A1 to 2 decimal places first, then A2 =LOOKUP(ROUND(A1;2);D1:12;E1:E12) And as someone else pointed out previously on this list, it's a good practise to use range names for clarity. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
