can a single cell contain multiple numbers?

You don't need a table for your example. If the age is entered in cell A! then

=(A1>=10)*(A1<=20)*3+(A1>=21)*(A1<=30)*4+(A1>=31)*5

will yield the correct dollar amount
--
Gary's Student


"jw446" wrote:

> Trying to create a table that will pull a price based on a value within a
> range. Trick is I'm trying not to enter the entire range into the pricelist.
> Example logic is "If you're aged 10-20, then you can enter for $3. If
> you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> for $5."

Try this formula:

=VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)

Note the curly braces, the commas, and the semicolons.
The braces define an array.
The commas separate values in a row.
The semicolons are like carriage returns and begin a new row.

Effectively, the values within the braces are the equivalent of a 2 column
range of values:
_0____0
10____3
21____4
31____5

Does that help?

--
Regards,
Ron


"jw446" wrote:

> Trying to create a table that will pull a price based on a value within a
> range. Trick is I'm trying not to enter the entire range into the pricelist.
> Example logic is "If you're aged 10-20, then you can enter for $3. If
> you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> for $5."

Very helpful thanks. Curly braces are half my battle, I think. Is there any
way to replace the hard-coded values in the array with cell references?

Many thanks.

"Ron Coderre" wrote:

> Try this formula:
>
> =VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)
>
> Note the curly braces, the commas, and the semicolons.
> The braces define an array.
> The commas separate values in a row.
> The semicolons are like carriage returns and begin a new row.
>
> Effectively, the values within the braces are the equivalent of a 2 column
> range of values:
> _0____0
> 10____3
> 21____4
> 31____5
>
> Does that help?
>
> --
> Regards,
> Ron
>
>
> "jw446" wrote:
>
> > Trying to create a table that will pull a price based on a value within a
> > range. Trick is I'm trying not to enter the entire range into the pricelist.
> > Example logic is "If you're aged 10-20, then you can enter for $3. If
> > you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> > for $5."

There sure is. Just put the table of values in a convenient place and refer
to it:

Example:
=VLOOKUP(A1,$D$1:$E$4,2,1)

Or, if you want to get a little fancier....name the range something like
LU_Rates.
(Select the range, then type LU_Rates in the Names box in the upper left of
the Excel window and press the [Enter] key)

Then you can refer to that range name in the formula:
=VLOOKUP(A1,LU_Rates,2,1)

Does that help?

--
Regards,
Ron


"jw446" wrote:

> Very helpful thanks. Curly braces are half my battle, I think. Is there any
> way to replace the hard-coded values in the array with cell references?
>
> Many thanks.
>
> "Ron Coderre" wrote:
>
> > Try this formula:
> >
> > =VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)
> >
> > Note the curly braces, the commas, and the semicolons.
> > The braces define an array.
> > The commas separate values in a row.
> > The semicolons are like carriage returns and begin a new row.
> >
> > Effectively, the values within the braces are the equivalent of a 2 column
> > range of values:
> > _0____0
> > 10____3
> > 21____4
> > 31____5
> >
> > Does that help?
> >
> > --
> > Regards,
> > Ron
> >
> >
> > "jw446" wrote:
> >
> > > Trying to create a table that will pull a price based on a value within a
> > > range. Trick is I'm trying not to enter the entire range into the pricelist.
> > > Example logic is "If you're aged 10-20, then you can enter for $3. If
> > > you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> > > for $5."

Think I got it now. Thanks.

"jw446" wrote:

> Very helpful thanks. Curly braces are half my battle, I think. Is there any
> way to replace the hard-coded values in the array with cell references?
>
> Many thanks.
>
> "Ron Coderre" wrote:
>
> > Try this formula:
> >
> > =VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)
> >
> > Note the curly braces, the commas, and the semicolons.
> > The braces define an array.
> > The commas separate values in a row.
> > The semicolons are like carriage returns and begin a new row.
> >
> > Effectively, the values within the braces are the equivalent of a 2 column
> > range of values:
> > _0____0
> > 10____3
> > 21____4
> > 31____5
> >
> > Does that help?
> >
> > --
> > Regards,
> > Ron
> >
> >
> > "jw446" wrote:
> >
> > > Trying to create a table that will pull a price based on a value within a
> > > range. Trick is I'm trying not to enter the entire range into the pricelist.
> > > Example logic is "If you're aged 10-20, then you can enter for $3. If
> > > you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> > > for $5."

Trying to create a table that will pull a price based on a value within a
range. Trick is I'm trying not to enter the entire range into the pricelist.
Example logic is "If you're aged 10-20, then you can enter for $3. If
you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
for $5."

You don't need a table for your example. If the age is entered in cell A! then

=(A1>=10)*(A1<=20)*3+(A1>=21)*(A1<=30)*4+(A1>=31)*5

will yield the correct dollar amount
--
Gary's Student


"jw446" wrote:

> Trying to create a table that will pull a price based on a value within a
> range. Trick is I'm trying not to enter the entire range into the pricelist.
> Example logic is "If you're aged 10-20, then you can enter for $3. If
> you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> for $5."

Try this formula:

=VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)

Note the curly braces, the commas, and the semicolons.
The braces define an array.
The commas separate values in a row.
The semicolons are like carriage returns and begin a new row.

Effectively, the values within the braces are the equivalent of a 2 column
range of values:
_0____0
10____3
21____4
31____5

Does that help?

--
Regards,
Ron


"jw446" wrote:

> Trying to create a table that will pull a price based on a value within a
> range. Trick is I'm trying not to enter the entire range into the pricelist.
> Example logic is "If you're aged 10-20, then you can enter for $3. If
> you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> for $5."

Very helpful thanks. Curly braces are half my battle, I think. Is there any
way to replace the hard-coded values in the array with cell references?

Many thanks.

"Ron Coderre" wrote:

> Try this formula:
>
> =VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)
>
> Note the curly braces, the commas, and the semicolons.
> The braces define an array.
> The commas separate values in a row.
> The semicolons are like carriage returns and begin a new row.
>
> Effectively, the values within the braces are the equivalent of a 2 column
> range of values:
> _0____0
> 10____3
> 21____4
> 31____5
>
> Does that help?
>
> --
> Regards,
> Ron
>
>
> "jw446" wrote:
>
> > Trying to create a table that will pull a price based on a value within a
> > range. Trick is I'm trying not to enter the entire range into the pricelist.
> > Example logic is "If you're aged 10-20, then you can enter for $3. If
> > you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> > for $5."

There sure is. Just put the table of values in a convenient place and refer
to it:

Example:
=VLOOKUP(A1,$D$1:$E$4,2,1)

Or, if you want to get a little fancier....name the range something like
LU_Rates.
(Select the range, then type LU_Rates in the Names box in the upper left of
the Excel window and press the [Enter] key)

Then you can refer to that range name in the formula:
=VLOOKUP(A1,LU_Rates,2,1)

Does that help?

--
Regards,
Ron


"jw446" wrote:

> Very helpful thanks. Curly braces are half my battle, I think. Is there any
> way to replace the hard-coded values in the array with cell references?
>
> Many thanks.
>
> "Ron Coderre" wrote:
>
> > Try this formula:
> >
> > =VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)
> >
> > Note the curly braces, the commas, and the semicolons.
> > The braces define an array.
> > The commas separate values in a row.
> > The semicolons are like carriage returns and begin a new row.
> >
> > Effectively, the values within the braces are the equivalent of a 2 column
> > range of values:
> > _0____0
> > 10____3
> > 21____4
> > 31____5
> >
> > Does that help?
> >
> > --
> > Regards,
> > Ron
> >
> >
> > "jw446" wrote:
> >
> > > Trying to create a table that will pull a price based on a value within a
> > > range. Trick is I'm trying not to enter the entire range into the pricelist.
> > > Example logic is "If you're aged 10-20, then you can enter for $3. If
> > > you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> > > for $5."

Think I got it now. Thanks.

"jw446" wrote:

> Very helpful thanks. Curly braces are half my battle, I think. Is there any
> way to replace the hard-coded values in the array with cell references?
>
> Many thanks.
>
> "Ron Coderre" wrote:
>
> > Try this formula:
> >
> > =VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)
> >
> > Note the curly braces, the commas, and the semicolons.
> > The braces define an array.
> > The commas separate values in a row.
> > The semicolons are like carriage returns and begin a new row.
> >
> > Effectively, the values within the braces are the equivalent of a 2 column
> > range of values:
> > _0____0
> > 10____3
> > 21____4
> > 31____5
> >
> > Does that help?
> >
> > --
> > Regards,
> > Ron
> >
> >
> > "jw446" wrote:
> >
> > > Trying to create a table that will pull a price based on a value within a
> > > range. Trick is I'm trying not to enter the entire range into the pricelist.
> > > Example logic is "If you're aged 10-20, then you can enter for $3. If
> > > you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
> > > for $5."