first 2 number

[Shing]

Hi ,

I have an excel base like this :

Part Number Description Cost Markup(1)
AA-COA-BC Product 1 10.00

I would like to create a new column called "category" which is the first 2
letter of the part number. How can I do that please ?

Also, I would like to have a formula to calculate Markup(1)

If Cost is < 49 >0 then Markup(1)=Cost +5
AND
If Cost is >49 <249 then Markup(1)=Cost+12
And
If Cost is >249<499 then Markup(1)=Cost+18
And
If Cost is >499 < 999 then Markup(1)=Cost +28
And
If Cost is >999 <1500 then Markup(1)=Cost+35
And
If Cost is >1500 then Markup(1)=Cost+40

I don't know if I ask too much. But please help.

Thanks

 

[Frank Kabel]

Hi
(1): =LEFT(A1,2)

(2): First create a lookup table (e.g. on a separate sheet called
'lookup'):
A B
1 0 5
2 49 12
3 249 18
.....
Note: In your example you have not defined what should happen for 0,
49, 249, etc. So I assumed 49 would yield to a markup of 12, etc.

Now use the following formula
=C1+VLOOKUP(C1,'lookup'!$A$1:$B$100,2,TRUE)

 

[Shing]

HI Frank,

Really appreciate your quick response.

Shing