Calculate depending on a price range

**matorac** · 03-29-2007, 12:27 PM

Hi!
I have price ranges for some bussines:

if 1 to 4 rooms = 100 euros per room
if 5 to 8 rooms = 90 euros per room
if 9 to 12 rooms = 80 euros per room
if 13 rooms and more = 70 euros per room

In one cell, I put a number of rooms (ex. 6) which means it costs 90 euros per room, the second cell should give me the result of 6*90 which is 540 euros.

examples:
if |cell A1= 9| then |cell A2= 9*80= |
if |cell A1= 2| then |cell A2= 2*100= |
if |cell A1= 18| then |cell A2= 18*70= |
etc.

Is it possible to make a formula that counts on this principle?
Thanx in advance, and sorry for my lousy english.

**daddylonglegs** · 03-29-2007, 12:29 PM

Try this formula in A2

=LOOKUP(A1,{0,5,9,13;100,90,80,70})*A1

**matorac** · 03-29-2007, 12:40 PM

Originally Posted by VBA Noob

Please read forum rules below and then amend your title

VBA Noob

I'm sorry and I hope now it's ok.

**matorac** · 03-30-2007, 03:04 AM

Originally Posted by daddylonglegs

Try this formula in A2

=LOOKUP(A1,{0,5,9,13;100,90,80,70})*A1

Thanks, but it doesn't work! It displays an error:

**daddylonglegs** · 03-30-2007, 04:38 AM

Some versions of Excel (usually non-English) use different notation. The above works for me but it looks like you need to use ; instead of , i.e.

=LOOKUP(A1;{0,5,9,13;100,90,80,70})*A1

**matorac** · 03-30-2007, 05:50 AM

Originally Posted by daddylonglegs

Some versions of Excel (usually non-English) use different notation. The above works for me but it looks like you need to use ; instead of , i.e.

=LOOKUP(A1;{0,5,9,13;100,90,80,70})*A1

Error again, but this time Excel corrected the formula into this:

=LOOKUP(A1;{0,5913;100,90807})*A1

If I choose no correction, it highlight numbers 0,5,9,13 (see attach).

I really appreaciate your efforts and help.

**daddylonglegs** · 03-30-2007, 06:26 AM

Again, I assume that this is a difference in notation between your version of excel and mine, because of that I am unable to check whether this suggestion will work

but try

=LOOKUP(A1;{0;5;9;13};{100;90;80;70})*A1

If that doesn't work then try putting the value in the worksheet, e.g. 0, 5, 9 and 13 in Y1:Y4 and 100, 90, 80 and 70 in Z1:Z4 and use

=LOOKUP(A1;Y1:Z4)*A1

**matorac** · 03-30-2007, 02:04 PM

Originally Posted by daddylonglegs

Again, I assume that this is a difference in notation between your version of excel and mine, because of that I am unable to check whether this suggestion will work

but try

=LOOKUP(A1;{0;5;9;13};{100;90;80;70})*A1

If that doesn't work then try putting the value in the worksheet, e.g. 0, 5, 9 and 13 in Y1:Y4 and 100, 90, 80 and 70 in Z1:Z4 and use

=LOOKUP(A1;Y1:Z4)*A1

Yes, the bold part finaly works.

Thanx a lot!

Calculate depending on a price range

LinkBack

Thread Tools

Rate This Thread

Display

Calculate depending on a price range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions