Data Range with more than value

[Dolores Gomez]

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.

 

[Luke M]

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)

 

[Eduardo]

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

 

[Dolores Gomez]

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

 

[Pete_UK]

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

 

[Dolores Gomez]

Thanks, this really helped

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]

Thanks, I will try it!!

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



.

 

[Pete_UK]

You're welcome, Delores.

Pete

Thanks, I will try it!!








- Show quoted text -