lookup formulas dependent upon lookup formulas

S

Skibee

Hi

I have the following formula:
=LOOKUP(L7,{0,15,15.1,15.4,15.5,15.9,16,16.9,17,17.9,18,18.9,19,19.9,20,20.9,21,21.9,22,22.9,23,23.9,24,24.9,25.0259},{"1.00","1.00","2.50","2.50","4.00","4.00","5.50","5.50","6.50","6.50","7.00","7.00","8.75","8.75","9.50","9.50","10.75","10.75","12.00","12.00","13.25","13.25","14.50""14.50","15.75","15.75"})

So the result of this lookup operation may be:

L7=2.50
L8=9.50
L9=13.25
and so on down to L650

Now I need to take this initial look up result and say if:
L7=1.00 return 0.00
if 2.50 return 1.75
if 4.00 return 3.50
if 5.50 return £4.50
and so on with 13 possible answers.

I have tried to do this using a further look-up formula, but it just returns
#NA! even when the L7 or L8 etc. is populated. Does anyone know how I get
round this?
Many thanks for any help.
Regards Liz
 
D

driller

Without knowing where you are headed,

for a starter, i just insert the new condition u like but without removing
any possible logical conflict from your posted formula...I just insert the
new lookup_vector and Result_vector in the braced fields...
Now I need to take this initial look up result and say if:
L7=1.00 return 0.00
if 2.50 return 1.75
if 4.00 return 3.50
if 5.50 return £4.50

test this one..
=lookup(L7,{1,0,1.75,3.5,4.5,1,2.5,2.5,4,4,5.5,5.5,6.5,6.5,7,7,8.75,8.75,9.5,9.5,10.75,10.75,12,12,13.25,13.25,14.5,14.5,15.75,15.75},{0,1,2.5,4,5.5,15,15.1,15.4,15.5,15.9,16,16.9,17,17.9,18,18.9,19,19.9,20,20.9,21,21.9,22,22.9,23,23.9,24,24.9,25,25.9})

i intentionally remove all the "quotes" to read easily for testing...can
insert them back afterwards...

--
regards,
driller

*****
- dive with Jonathan Seagull
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top