How to round a column in excel up or down?
Hey guys, I did a percentage formula recently to lower excel prices by a specific percentage.
But the prices are all whacky, I for example I have a gasket part for my store thats at 36.29 when I lowered by a percent in excel.
But I would like to round it to 39.99, but I have this situation with… 700 parts in one column. So doing by hand will take awhile.
Is there a way to round up?
The other answer so far indicates the full problem you may face but does not discuss it. The way you adjust the price probably changes as the price increases. At some levels you will probably want to round up to the nearest dollar (less a penny in every case), as the price goes up then to the nearest two, five, ten, twenty etc. That is difficult, though not impossible, to create a single formula for that. The biggest problem with such a formula would be verifying it and changing it.
A simpler approach would involve creating a table of price points and using Vlookup to adjust any calculated price to the next higher price point. This would give you complete flexibility in setting your price points at every level. Here is how it would work.
In a separate worksheet (we will use Sheet2 here)
In Cell B1 enter (the header) Price Point
In consecutive cells below B1 enter your price points (e.g. .49, .99, 1.49, 1.99, etc.) in ascending order.
You can enter every one or use formulas to increase your price point by a set amount for a range of values. The object is to create a visible list of every possible price point you might want to retrieve from this table. The last entry will be the highest price point returned. Any calculated price greater than that will be reduced to that amount.
In Cell A1 enter (the header) Calculated Price
In Cell A2 enter -0.01
In Cell A3 enter =B2
Copy cell A3 down as far as there are entries in Column B
Now wherever you want a price point based on a calculated price enter
=VLOOKUP(A1-0.01,Sheet2!A2:B100,2,TRUE) replacing A1 with the name of the cell containing the calculated price.
The original setup may take some time but you will be able to see and easily adjust your price points (if you have used formulas to increase your price points by fifty cents or a dollar over specific ranges it would be very simple to change every thing to end with an 8 instead of a 9).
When you have finished the price point table you should unlock the cells in column B that do not contain formulas (do not unlock the first row) and then protect the worksheet to prevent accidental changes to the table and to help ensure that you enter price point changes in the column titled Price Point.
+++++++++++++++++++++++++++++++++++++++
NOTE: Significant simplifying changes were made to the above at 9:55 AM central time Sep 5. Please carefully reread if you read this before that time.
HHO Dry Cell Mars Gasket Part 3
|
|
ONE KAWASAKI GASKET CYLINDER BASE PART 11060-1171 NOS $3.95 |
|
|
ONE GENUINE TECUMSEH GASKET PART 36352 NEW OLD STOCK $1.45 |
|
|
ONE BRIGGS & STRATTON GASKET – PUMP PART 271025 NEW OLD STOCK WITH FREE SHIPPING $3.07 |
|
|
Replacement part Wheel Horse Lawnboy Toro Lawn mower 604116 S Gasket $0.99 |
|
|
ONE TECUMSEH GASKET – BREATHER PART 31619A NEW OLD STOCK WITH FREE SHIPPING $3.20 |
|
|
ONE GENUINE BRIGGS & STRATTON GASKET – INTAKE PART 270070 NEW OLD STOCK $2.95 |
|
|
ONE GENUINE WALBRO GASKET – RETAIN BOWL PART 92-17-1 NEW OLD STOCK $1.00 |
|
|
ONE GENUINE TECUMSEH GASKET PART 35762, NEW OLD STOCK WITH FREE SHIPPING $3.95 |
|
|
genuine Tecumseh engine 34923A head gasket HM100 TVXL220 HMSK100 engine part nos $7.99 |
|
|
ONE GENUINE BRIGGS & STRATTON GASKET – CYLINDER HEAD PART 27670 & 272167 NOS $1.95 |
|
|
One Briggs & Stratton Gasket Intake Part 270972 New Old Stock With Free Shipping $3.20 |
|
|
STIHL CYLINDER GASKETS PART NUMBER 4140 029 2300 $9.99 |
|
|
STIHL FS TRIMMER INTAKE MANIFOLD GASKETS PART NUMBER 4137 129 0905 A $9.99 |
|
|
TORO Lawn Mower Part Number: 608362 gasket $4.99 |
|
|
TORO Lawn Mower Part Number: 678071 Gasket Set $12.99 |
|
|
TORO Lawn Mower Part Number: 54-6200 Gasket $7.99 |
|
|
TORO Lawn Mower Part Number: 604283 Gasket $4.99 |
|
|
Tecumseh Diaphragm & Gasket, Part # 630925 $0.99 |
|
|
Tecumseh Cylinder Head Gasket, Part # 29953 B $0.99 |
|
|
Tecumseh Valve Cover & Breather Gasket, Part # 27896 $0.99 |
|
|
B&G Soft Seat Gasket – Part SP-159 for B&G Sprayers $14.99 |
|
|
HONDA Genuine Parts 21691-ZH8-800 New Reduction Cover Gasket $0.99 |
|
|
HONDA Genuine Parts 11381-ZL0-000 New Case Cover Gasket GX200QXC9 , MVH150GH $0.99 |
|
|
Set of 2 Briggs & Stratton Head Gasket Part No 271917. $0.99 |
|
|
Geninue Echo Part Intake Gasket – 13001642031 $11.95 |
|
|
Tecumseh Head Gasket 1 piece part # 34041 $12.99 |
|
|
Tecumseh Head Gasket part # 29953C $7.99 |
|
|
Geninue Shindaiwa Part Carburetor Diaphragm Gasket Kit 80255 $39.95 |
|
|
genuine Briggs Stratton 391662 gasket set engine repair part nos oem $13.99 |
|
|
Stihl MS361 Exhaust Gasket Heat Shield Parts OEM MS 361 1135 $6.99 |
|
|
Magic Bullet MBR-1701 17-Piece Express Mixing Set $44.99 Taking up no more space on the counter than a coffee mug, this convenient mixing system replaces a food processor, blender, and coffee grinder. Keep it handy on the countertop for instant meals and snacks. The 17-piece set includes a high-torque power base with a stainless-steel cross blade and flat blade, a tall and a short bullet cup, four party mugs with colored lip rings, a shaker top and a st… |
|
|
Rubber o-ring gasket seal for Oster & Osterizer, 3 PACK. $0.01 Flat rubber o-ring seal. A common replacement part, this seal sits between the top of the blade and the botom of the jar. Only one seal is needed per blender. Fits ALL Oster blenders, Osterizers and Oster Kitchen Centers. Also fits some Sunbeam models which use a metal drive pin. Package of 3 seals…. |
|
|
Waste King L-2600 Legend Series 1/2 HP Continuous Feed Operation Waste Disposer $154.00 The Waste King Legend 2600 1/2-Horsepower Disposer showcases a powerful motor, stainless steel grinding components, superior sound insulation, and a corrosion-proof grinding chamber for efficient, quiet, no-fuss waste disposal. Once installed, this continuous-feed waste disposer will more than earn its place in your kitchen. The Legend 2600 1/2-Horsepower Disposer At a Glance:2,600 RPM permanent m… |
|
|
King For A Day, Part 1 … |
|
|
Smelly Washer Inc. Washing Machine Cleaner, 24 Treatments $12.49 Smelly Washer Cleaner is guaranteed to eliminate bad odor from any front load or top load clothes washer. After cleaning your washing machine Smelly Washer is the only washing machine cleaner available that will also remove mildew odor from towels, fungus smell from clothing and musty odors from your other laundry! Go to our blog page for more information on how to remove washing machine smell and… |
|
|
The Sonic Seal for E-Series Sonicare Replacement Heads, 3 Count (Blue, Pink, Frost) $16.95 The Sonic Seal is the only waterproof cover for your Sonicare electric toothbrush. Seal those nooks and crannies on your replacement head and keep the inside dry. Made from 100% food-grade silicone, The Sonic Seal is tough and reusable. Rinse and go, it makes cleaning a breeze. No more gunk…guaranteed. Fits Sonicare models: Elite, Essence and Xtreme…. |
|
|
MieleCare Collection: Dishwasher Conditioner $12.95 Dishwashers are exposed to a lot of food residue including starch and fats. Regular use of Miele’s machine conditioner can actually help extend the life of your machine and ensure great wash performance is maintained. This special formula can help keep door gaskets supple and maintain a tight seal. Recommended use is every 2 months in an empty dishwasher…. |
|
|
Gates 41165 Water Pump $24.75 Water Pump… |
|
|
Fluke C1600 Gear Box for Meter and Accessories $45.40 METER GEAR BOX… |
|
|
Nexus 80mm Silicon Fan Noise Absorber – SA-80 $3.99 Nexus 80mm Silicon Fan Noise Absorber These Nexus ultra-soft silicon fan gaskets are designed to reduce the vibration from PC case fans and maintain a seal between the case side and the fan housing. Nexus Fan Noise Absorbers Stop Vibration A case fan in contact with the PC housing can cause vibration during operation. By placing a SA-Series noise absorber between the case and case fan you elimi… |
|
|
Hayward Skimmer Gasket $2.99 Hayward Skimmer Gasket is a replacement part for automatic skimmer. |
|
|
Delta Part RP42343 – Delta Replacement Part $4.22 D-GASKET |
|
|
Delta Part RP44769 – Delta Replacement Part $4.65 D-GASKET |
|
|
Delta Part RP43340 – Delta Replacement Part $1.31 D-GASKET |
|
|
Delta Part RP19420 – Delta Replacement Part $2.83 D-GASKET |
|
|
Sta-Rite Gasket – 051030101 $4.91 Sta-Rite Gasket is the replacement part for the Sta-Rite sun star light. |
|
|
Hayward Inlet Gasket $1.99 Hayward Inlet Gasket is a replacement part for Hayward fitting model: SP1408. |
|
|
Sta-Rite Gasket $2.99 Sta-Rite Gasket is a replacement part for Sta-Rite pump. |
|
|
Hayward Skimmer Gasket SPX1094G $1.99 Hayward Skimmer Gasket SPX1094G is a replacement part for SP1094 pool skimmer. |
|
|
Hayward Skimmer Gasket SPX1085D $5.93 Hayward Skimmer Gasket SPX1085D is a replacement part for SP1085 pool skimmer. |
|
|
Hayward Standard Butterfly Gasket $4.99 Hayward Standard Butterfly Gasket is a replacement part for automatic skimmer. |
|
|
Jandy Housing Gasket $14.32 Jandy Housing Gasket is replacement part of JHP and JHPU “Stealth Series” pump. |
|
|
Hayward Valve Key Gasket $7.99 Hayward Valve Key Gasket is a replacement part for multiport valves. |
|
|
Sierra Head Gasket Set $294.99 Sierra Head Gasket Set . Contains: Description Head Gasket Carb Mounting Gasket Carb Mounting Gasket Carb Mounting Gasket Intake Manifold Gasket Set Valve Cover Gasket (2) Valve Stem Seals (16) Distributor Mounting Gasket Part # 18-3877 18-0467 18-0463 18-0462 18-1231 18-0464 18-4024 18-0874 |
|
|
Jabsco Impeller Gasket For Diesel Motors $16.49 Jabsco Impeller Gasket For Diesel Motors . Diesel Motor Impeller Gasket. Part number 17365-1000 |
|
|
Sta-Rite Gasket HF Series $1.99 Sta-Rite Gasket is a replacement part for HF Series pump. |
|
|
Hayward Vinyl Niche Gasket SPX506D $8.29 Hayward Vinyl Niche Gasket SPX506D is a replacement part for SPX506D pool skimmer. |
|
|
Hayward Wide Mouth Skimmer Gasket $5.99 Hayward Wide Mouth Skimmer Gasket is replacement part for automatic skimmers. |
|
|
Hayward Wide Mouth Butterfly Gasket $7.99 Hayward Wide Mouth Butterfly Gasket is a replacement part for automatic skimmers. |
|
|
Delta Part RP47273 – Delta Replacement Part $14.53 D-SINGLE HOLE KIT GASKET ALLORA GASKET |
