Formula Help

[geno32080]

Good evening,
I have been tasked with creating a pricelist in SAP using excel. I exported the file, the file shows the cost of the item. My question is how can I use excel to create a specific margin based on cost.
The following is a list of cost and what margins to use.
.01 thru 1.99 mark up 80%
2.00 thru 8.99 mark up 70%
9.00 thru 25.00 mark up 60%
25.01 thru 399.00 mark up 50%
400.00 thru 2000.00 mark up 40%
then round up the price to the nearest dollar and minus .03 cents. So all prices end with $**.97.
This way I can make sure after the price file has been uploaded in the system it is figured the way it suppose to be.

Would somebody please help me figure this formula. I'm good with smaller formulas but the "if" thing is new for me.
Thank you in advance.

 

[geno32080]

All prices are being updated, Rather they stay the same or not

 

[geno32080]

So the VBA would run as the info was being entered?

 

[kweaver]

No. You run the macro when you need the price updates made in sheet 2.

 

[geno32080]

That's what I was hoping, I just need to adjust the cell colum and rows to fit the spread sheet.