Newbie ..which formulae for this please?

[www.ttdown.com]

Win98se
Office 2000 pro'

My newbie layout in excel s/s:

A B C D E
1 Seats COST 88 100

2 80-89 100
3 90-99 200
4 100-106 300

I have a variable formula cell (D1) which contains a number (between
80 and 106), say 88.

Now my wish is to look at cell D1, match it to B2, B3 or B4, and then
give me the result in another cell (say E1), which in my example
should give me the answer of 100 (from C2).
Another result could be : lookup 91, would produce tha answer of 200
(from B3).
Makes me confused just writing this so I hope you good people can
understand it.!!
I have tried Sumif etc. but fall down with the range bit (e.g 80-89),
where I tried 80:89.etc...did not like.?

TIA

 

[Frank Kabel]

Hi

first you should split the seat numbers in two different rows (lower
and upper boundary):
A B C
Seats (l) Seats (u) Costs
0 79 0
80 89 100
90 99 200
.....

Now if you have a number in lets say D1 and you want to evaluate the
respective cost in E1 enter the following formula in E1:
=VLOOKUP(D1,A2:C10,3, TRUE)

HTH
Frank

 

[www.ttdown.com]

Thank you Frank
Works OK until D1 alters via the formula in D1, when if the cell D1
has 92 in it then E1 = FALSE...instead of 200 !..so I need it to
reflect D1 change and give a true figure please.
Perhaps I have not explained correctly?...your reply was easy to
follow though.

ttd

 

[Frank Kabel]

Hi

the formula below works for me. If I enter 92 in D1 E1 wikll return
200. I'm not so sure what you mean with 'alters via the formula...)
If you like, you can mail me your sheet and I'll have a look at it

Frank

 

[www.ttdown.com]

OK Frank....
for various reasons I prefer not to Email.....but grateful of the
offer.
Now cell D1 is a "sum" total that will vary from 80 to 106, that is
what I meant by saying 'alters via the formula....
HTH to explain further.
The example I gave was to simplify for the post purpose, but actually
some cells are in different parts of the s/s, but still relative to
the post.

ttd

 

[Frank Kabel]

Hi
1. If D1 is changed manually or via formula does not affect the result
in E1
2. You get a 'FALSE' as result in E1. If yes I think the formula is not
correct.

One suggestion: Copy the formula in E1 and D1 and post these entries

Frank

 

[www.ttdown.com]

Frank
Apologies...C2, C3, C4. had formulae which "threw" your answer in my
calc's.
Corrected and works a dream.
I will now develope it further to include other calculations in the
large s/s.
Thanks for you very quick and simpathetic posts.

Regards
ttd

 

[Frank Kabel]

Hi

you're welcome
Regards
Frank

Frank
Apologies...C2, C3, C4. had formulae which "threw" your answer in my
calc's.
Corrected and works a dream.
I will now develope it further to include other calculations in the
large s/s.
Thanks for you very quick and simpathetic posts.

Regards
ttd