Any MS Excel Guru's? - Help Please

[White-Light]

This is what you end up with:



Uploaded with ImageShack.us

In the above example, I've displayed to 0 decimal points in the column.

I've displayed to 2 decimal points in the sub row.

I've rounded the sub total UP in the Final row.

You can also command down rounding the same way ie = ROUNDDOWN(A5,0)

[ShortyInCanada]

Or if you want to try this...
I spend the time setting up the first cell of groups exactly as I want it to work out. Using "round", defining decimal places (I usually use 3 actually) and whatnot.
Then I use the format shortcut key (paintbrush on the menu bar ?), which you can click on twice, to allow you to copy the format of the cell to any other cells. The mouse indicator will change to what looks like a line with a paintbrush, I think (Don't use MS at work. In open office its a paintcan for the mouse.) and you can just paint the format over any/all cells you want it to apply to.
This allows you to NOT select certain cells that you don't want to have in that format.

I use this method on spreadsheets all the time. Several different styles of formats in columns across multiple pages of data.
P.S. I work in warehousing and have to work with lots of numbers, quantities, percentages, dollar values, estimates, freight costs, etc etc etc. Without spreadsheets I would be crippled by all the data required every week.
Of course I also like to layout the formats BEFORE I enter the data usually just to see if the further entry of numbers is exactly as I want it.
Hope this helps you out.

[Jem]

Thanks again guys

I'll take a look at the other suggestions over the next couple of days. I had a deadline to meet today, hence the rush to get it sorted. That deadline has now passed and the spreadsheet results have been printed and distributed. Next deadline is in a month's time, so maybe i'll have it tidied up by then

Cheers

Jem

[dnar]

An easier way is to have your full price with decimals in one column (lets say column B), add another column (C) that simply references the previous column cells with the round formula. Sum column C and then Hide column B.



Column B is now hidden and column C which is rounded is visible.

[Jem]

Thanks Wayne

Done it

[dnar]

Excel is about the only program Microsoft ever wrote that I actually like.

Hang on, Microsoft didn't actually write it (originally).

If you want examples of total piles of crappola: MS Project and Hyperterminal...

[Jem]

Haha, don't knock Hyperterminal, it's got me out of a hole many times. Besides it's still useful for sending commands to some Coherent lasers

[dnar]

Check out Bray's terminal (free). it get shte baud rate right (unlike hyperslug) and you can change port setting son the fly, plus HEX output, scipting, key macros etc. A great debugging tool.

https://sites.google.com/site/terminalbpp/

[Jem]

Nice, i'll give it a go

[LaNeK779]

the only proper way to do this is what WhiteLight suggests, use round, roundup or rounddown accordingly, then format the cells to show as many decimal points (zerros in your case) as you want. you can see in more detail how these functions work using excel help. it is a help file that actually makes sense!!

another usefull function when working with pricelists (or any other list) you might want to search is "vlookup". it is usefull if you have two lists, who are not in the same order or don;t have the same length, but share a common column (i.e. product code)