| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP question concerning population a price
If I had a seat list and price list according to age;
Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
I meant to type "Population a price"
"DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
One simple way is to set-up a "cross-tab" price table,
then use index/match Assume the price table below is set-up in A1:C4 PriceTable 0 18 Front 35 40 Balcony 10 15 Floor 20 30 Then if you have the age/seat running in E2:F2 down, eg: 12 Balcony 19 Front 18 Floor you could place this in G2: =IF(COUNTA(E2:F2)<2,"",INDEX($B$2:$C$4,MATCH(F2,$A $2:$A$4,0),MATCH(E2,$B$1:$C$1))) and copy G2 down to return the correct prices, viz.: 12 Balcony 10 19 Front 40 18 Floor 30 -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "DLL" wrote: I meant to type "Population a price" "DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
DLL wrote:
I meant to type "Population a price" "DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS One way: Make a little table like this in Sheet1!A1:C4 18+ under 18 Floor 30 20 Balcony 15 10 Front Row 40 35 In Sheet2 set up column labels and search criteria, e.g.: Seating Age Price Balcony under 18 ? The ? formula in C2 is =INDEX(Sheet1!$B$2:$C$4,MATCH(Sheet2!A2,Sheet1!$A$ 2:$A$4,0),MATCH(Sheet2!B2,Sheet1!$B$1:$C$1,0)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
Thanks but I actually need to use the VLOOKUP formula, I had success doing it
the way you described. "Max" wrote: One simple way is to set-up a "cross-tab" price table, then use index/match Assume the price table below is set-up in A1:C4 PriceTable 0 18 Front 35 40 Balcony 10 15 Floor 20 30 Then if you have the age/seat running in E2:F2 down, eg: 12 Balcony 19 Front 18 Floor you could place this in G2: =IF(COUNTA(E2:F2)<2,"",INDEX($B$2:$C$4,MATCH(F2,$A $2:$A$4,0),MATCH(E2,$B$1:$C$1))) and copy G2 down to return the correct prices, viz.: 12 Balcony 10 19 Front 40 18 Floor 30 -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "DLL" wrote: I meant to type "Population a price" "DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
Thanks that seems to work, but I really need to use the VLOOKUP formula. It's
for a class. Not the exact problem but similar. Thanks for any help "smartin" wrote: DLL wrote: I meant to type "Population a price" "DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS One way: Make a little table like this in Sheet1!A1:C4 18+ under 18 Floor 30 20 Balcony 15 10 Front Row 40 35 In Sheet2 set up column labels and search criteria, e.g.: Seating Age Price Balcony under 18 ? The ? formula in C2 is =INDEX(Sheet1!$B$2:$C$4,MATCH(Sheet2!A2,Sheet1!$A$ 2:$A$4,0),MATCH(Sheet2!B2,Sheet1!$B$1:$C$1,0)) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
Ok then. Here are several hints (^:
First, you know that VLOOKUP requires a single column of values to serve as a "key", right? But you have /two/ criteria that make up the key (Seating and Age). What to do? Here's what... Revise the little table in Sheet1 so it looks like this: A B C D Seating Age Key Price Floor 18OO ? 30 Floor U18 ? 20 Balcony 18OO ? 15 ..... The first formula ? is = A2&B2 Now, in Sheet2, you also have columns for A B C D Seating Age ... Price Can you see what to do next? N.B. I changed the Age values to simplify thing a little. DLL wrote: Thanks that seems to work, but I really need to use the VLOOKUP formula. It's for a class. Not the exact problem but similar. Thanks for any help "smartin" wrote: DLL wrote: I meant to type "Population a price" "DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS One way: Make a little table like this in Sheet1!A1:C4 18+ under 18 Floor 30 20 Balcony 15 10 Front Row 40 35 In Sheet2 set up column labels and search criteria, e.g.: Seating Age Price Balcony under 18 ? The ? formula in C2 is =INDEX(Sheet1!$B$2:$C$4,MATCH(Sheet2!A2,Sheet1!$A$ 2:$A$4,0),MATCH(Sheet2!B2,Sheet1!$B$1:$C$1,0)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
I really need to use the VLOOKUP formula.
It's for a class. Using Max's table in A1:C4 Lookup values: A10 = location B10 = age =VLOOKUP(A10,A1:C4,MATCH(B10,A1:C1),0) -- Biff Microsoft Excel MVP "DLL" wrote in message ... Thanks but I actually need to use the VLOOKUP formula, I had success doing it the way you described. "Max" wrote: One simple way is to set-up a "cross-tab" price table, then use index/match Assume the price table below is set-up in A1:C4 PriceTable 0 18 Front 35 40 Balcony 10 15 Floor 20 30 Then if you have the age/seat running in E2:F2 down, eg: 12 Balcony 19 Front 18 Floor you could place this in G2: =IF(COUNTA(E2:F2)<2,"",INDEX($B$2:$C$4,MATCH(F2,$A $2:$A$4,0),MATCH(E2,$B$1:$C$1))) and copy G2 down to return the correct prices, viz.: 12 Balcony 10 19 Front 40 18 Floor 30 -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "DLL" wrote: I meant to type "Population a price" "DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP question concerning population a price
Any way to talk in a formula? I'm a little green in excel. Thanks for your
help, I know it's a pain. "smartin" wrote: Ok then. Here are several hints (^: First, you know that VLOOKUP requires a single column of values to serve as a "key", right? But you have /two/ criteria that make up the key (Seating and Age). What to do? Here's what... Revise the little table in Sheet1 so it looks like this: A B C D Seating Age Key Price Floor 18OO ? 30 Floor U18 ? 20 Balcony 18OO ? 15 ..... The first formula ? is = A2&B2 Now, in Sheet2, you also have columns for A B C D Seating Age ... Price Can you see what to do next? N.B. I changed the Age values to simplify thing a little. DLL wrote: Thanks that seems to work, but I really need to use the VLOOKUP formula. It's for a class. Not the exact problem but similar. Thanks for any help "smartin" wrote: DLL wrote: I meant to type "Population a price" "DLL" wrote: If I had a seat list and price list according to age; Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if you are under 18. Then you have a front row seat that is $40.00 dollars if you are 18 and over and $35.00 if you are under 18. I have to determine the price according to age and seat selection. Any help is sure appreciated I am missing some step but can not figure out where. I hope I have given enough information. If not let me know...THANKS One way: Make a little table like this in Sheet1!A1:C4 18+ under 18 Floor 30 20 Balcony 15 10 Front Row 40 35 In Sheet2 set up column labels and search criteria, e.g.: Seating Age Price Balcony under 18 ? The ? formula in C2 is =INDEX(Sheet1!$B$2:$C$4,MATCH(Sheet2!A2,Sheet1!$A$ 2:$A$4,0),MATCH(Sheet2!B2,Sheet1!$B$1:$C$1,0)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Max Price and Min Price paid for an item - Rephrsed | Excel Worksheet Functions | |||
| Question on how to raise a price 15% in a coloum | Excel Discussion (Misc queries) | |||
| Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
| calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
| create a formula for price * discount* tax =final price | Excel Discussion (Misc queries) | |||
Linear Mode
