Data Range with more than value

  • Thread starter Thread starter Dolores Gomez
  • Start date Start date
D

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.
 
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)
 
Hi dolores,
could you please let me know the logic to get to 100, post other example,
will it be always PP
 
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
 
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
 
Thanks, this really helped

Luke M said:
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



.
Click to expand...
 
Thanks, I will try it!!

Pete_UK said:
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



.
Click to expand...
 
Back
Top