On Sunday 26 February 2006 20:58, Ross Johnson wrote: > 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]
Thanks for the help! I don't know how long it would have taken to figure this out without some help. It works great -- Jack Gates 864-335-8435 864-238-9300 I sell Honda cars Minds are like parachutes, they only work when they are open. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
