| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
| When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
| Show Data In Range not appearing in Separate Range | Excel Discussion (Misc queries) | |||
| How do I change a range name back to the underlying data range? | Excel Worksheet Functions | |||
| Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
Hybrid Mode
