Data Range with more than value
 

I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE PP
$0.01 TO $1 PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.

Data Range with more than value
 

Break the range up into two columns (Data - Text to columns). Assuming you
don't have gaps/overlaps in your price range (why would you?) your formula
can then become something like:
=LOOKUP(Value_to_find,A2:A10,C2:C10)

--
Best Regards,

Luke M
"Dolores Gomez" <Dolores wrote in message
...
I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE PP
$0.01 TO $1 PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it
to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.

Data Range with more than value
 

Hi dolores,
could you please let me know the logic to get to 100, post other example,
will it be always PP

"Dolores Gomez" wrote:

I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE PP
$0.01 TO $1 PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.

Data Range with more than value
 

The code listed under "PP" will always start with PP (it ties in to a
database that our company uses) so the column "PP" is already established and
no changes need to be made. I will be using the information listed below in
a separate worksheet that will include pricing and like my previous example
showed, If I enter .95 I want the column next to it to automatically be
populated with "PP0100".

Thanks for your quick response.


PRICE POINT/RANGE PP
$0.01 TO $1 PP0100
$1.01 TO $2 PP0105
$2.01 TO $3 PP0110
$3.01 TO $4 PP0115
$4.01 TO $5 PP0120
$5.01 TO $6 PP0125
$6.01 TO $7 PP0130
$7.01 TO $8 PP0135
$8.01 TO $9 PP0140
$9.01 TO $10 PP0145
$10.01 TO $11 PP0150
$11.01 TO $12 PP0155
$12.01 TO $13 PP0160
$13.01 TO $14 PP0165
$14.01 TO $15 PP0170
$15.01 TO $16 PP0175
$16.01 TO $17 PP0180

"Eduardo" wrote:

Hi dolores,
could you please let me know the logic to get to 100, post other example,
will it be always PP

"Dolores Gomez" wrote:

I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE PP
$0.01 TO $1 PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.

Data Range with more than value
 

If your price is in A2, you could put this in B2 (for example):

="PP01"&TEXT((ROUNDUP(A2,0)-1)*5,"00")

to give you one of the PP codes in your table.

If you have other codes above PP0180 then it may need some adjustment,
depending on the ranges.

Hope this helps.

Pete

On Mar 31, 6:29*pm, Dolores Gomez
wrote:
The code listed under "PP" will always start with PP (it ties in to a
database that our company uses) so the column "PP" is already established and
no changes need to be made. *I will be using the information listed below in
a separate worksheet that will include pricing and like my previous example
showed, If I enter .95 I want the column next to it to automatically be
populated with "PP0100".

Thanks for your quick response.

PRICE POINT/RANGE * * * PP
$0.01 TO $1 * * PP0100
$1.01 TO $2 * * PP0105
$2.01 TO $3 * * PP0110
$3.01 TO $4 * * PP0115
$4.01 TO $5 * * PP0120
$5.01 TO $6 * * PP0125
$6.01 TO $7 * * PP0130
$7.01 TO $8 * * PP0135
$8.01 TO $9 * * PP0140
$9.01 TO $10 * *PP0145
$10.01 TO $11 * PP0150
$11.01 TO $12 * PP0155
$12.01 TO $13 * PP0160
$13.01 TO $14 * PP0165
$14.01 TO $15 * PP0170
$15.01 TO $16 * PP0175
$16.01 TO $17 * PP0180



"Eduardo" wrote:
Hi dolores,
could you please let me know the logic to get to 100, post other example,
will it be always PP

"Dolores Gomez" wrote:

I am trying to assign codes to one of my worksheets based on pricing. *The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE * * * * PP
$0.01 TO $1 * * *PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). *This formula/function should give me
the "PP" code.- Hide quoted text -

- Show quoted text -

Data Range with more than value
 

Thanks, this really helped

"Luke M" wrote:

Break the range up into two columns (Data - Text to columns). Assuming you
don't have gaps/overlaps in your price range (why would you?) your formula
can then become something like:
=LOOKUP(Value_to_find,A2:A10,C2:C10)

--
Best Regards,

Luke M
"Dolores Gomez" <Dolores wrote in message
...
I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE PP
$0.01 TO $1 PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it
to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.


.

Data Range with more than value
 

Thanks, I will try it!!

"Pete_UK" wrote:

If your price is in A2, you could put this in B2 (for example):

="PP01"&TEXT((ROUNDUP(A2,0)-1)*5,"00")

to give you one of the PP codes in your table.

If you have other codes above PP0180 then it may need some adjustment,
depending on the ranges.

Hope this helps.

Pete

On Mar 31, 6:29 pm, Dolores Gomez
wrote:
The code listed under "PP" will always start with PP (it ties in to a
database that our company uses) so the column "PP" is already established and
no changes need to be made. I will be using the information listed below in
a separate worksheet that will include pricing and like my previous example
showed, If I enter .95 I want the column next to it to automatically be
populated with "PP0100".

Thanks for your quick response.

PRICE POINT/RANGE PP
$0.01 TO $1 PP0100
$1.01 TO $2 PP0105
$2.01 TO $3 PP0110
$3.01 TO $4 PP0115
$4.01 TO $5 PP0120
$5.01 TO $6 PP0125
$6.01 TO $7 PP0130
$7.01 TO $8 PP0135
$8.01 TO $9 PP0140
$9.01 TO $10 PP0145
$10.01 TO $11 PP0150
$11.01 TO $12 PP0155
$12.01 TO $13 PP0160
$13.01 TO $14 PP0165
$14.01 TO $15 PP0170
$15.01 TO $16 PP0175
$16.01 TO $17 PP0180



"Eduardo" wrote:
Hi dolores,
could you please let me know the logic to get to 100, post other example,
will it be always PP

"Dolores Gomez" wrote:

I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE PP
$0.01 TO $1 PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.- Hide quoted text -

- Show quoted text -

.

Data Range with more than value
 

You're welcome, Delores.

Pete

On Mar 31, 8:13*pm, Dolores Gomez
wrote:
Thanks, I will try it!!



"Pete_UK" wrote:
If your price is in A2, you could put this in B2 (for example):

="PP01"&TEXT((ROUNDUP(A2,0)-1)*5,"00")

to give you one of the PP codes in your table.

If you have other codes above PP0180 then it may need some adjustment,
depending on the ranges.

Hope this helps.

Pete

On Mar 31, 6:29 pm, Dolores Gomez
wrote:
The code listed under "PP" will always start with PP (it ties in to a
database that our company uses) so the column "PP" is already established and
no changes need to be made. *I will be using the information listed below in
a separate worksheet that will include pricing and like my previous example
showed, If I enter .95 I want the column next to it to automatically be
populated with "PP0100".

Thanks for your quick response.

PRICE POINT/RANGE * * * PP
$0.01 TO $1 * * PP0100
$1.01 TO $2 * * PP0105
$2.01 TO $3 * * PP0110
$3.01 TO $4 * * PP0115
$4.01 TO $5 * * PP0120
$5.01 TO $6 * * PP0125
$6.01 TO $7 * * PP0130
$7.01 TO $8 * * PP0135
$8.01 TO $9 * * PP0140
$9.01 TO $10 * *PP0145
$10.01 TO $11 * PP0150
$11.01 TO $12 * PP0155
$12.01 TO $13 * PP0160
$13.01 TO $14 * PP0165
$14.01 TO $15 * PP0170
$15.01 TO $16 * PP0175
$16.01 TO $17 * PP0180

"Eduardo" wrote:
Hi dolores,
could you please let me know the logic to get to 100, post other example,
will it be always PP

"Dolores Gomez" wrote:

I am trying to assign codes to one of my worksheets based on pricing. *The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below

PRICE POINT/RANGE * * * * PP
$0.01 TO $1 * * *PP0100

I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). *This formula/function should give me
the "PP" code.- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -