Results 1 to 1 of 1
Thread: Dollars and cents columns
2009-03-16, 11:26 #1
- Join Date
- Jan 2002
- Thanked 1 Time in 1 Post
For those who saw this earlier this morning this post has been revised and a new copy of the attachment uploaded in order to correct an error on the worksheet.
1:00 pm. I have now thought of a fix that solves all problems EXCEPT when rounding UP in the PRICE column. Version3 of the sheet is now attached.
About a month ago I posted a question asking if I could format a cell to ROUND. I wanted to show only full dollars or at least round in .25 increments on my price quote form. Our product tends to look silly when quoted at odd penny amounts.
I was of course informed that you can't FORMAT a cell to ROUND.....it has to be done by formula. A nice solution of entering my values in a separate cell in a column off the form and letting my price column round that cell was suggested. A nice solution but not convenient to navigate in this situation so it would not help here.
My solution is on the attached worksheet. It works well except for one problem which effects how I enter dollars and cents into the form when I actually NEED to enter a cents value.
To summarize the solution I forced the DOLLARS column to round up or down by formatting it to 0 places after the decimal point. I then set the "cents" column to always show ".00" as text when there was a dollar amount in the dollars column directly to the left.
After working with the form like this for a few weeks I modified it to what is attached. This adds the ability to enter a dollar amount in the DOLLARS column and then overwrite the ".00" in the CENTS column with any cents amount I might need. This isn't required very often, but on occasion is useful. The CENTS column is formatted as text which for some reason can still be multiplied by the formula in the TOTALS column. By formatting it as text I was able to enter a decimal and cents amount and have it display as needed.
The minor problem is that by rounding up the dollars column (by formatting it to have 0 places after the decimal) I will get the display of a price that is $1.00 higher then intended. Is there a NUMBER format I can choose that will always ROUND down (not really rounding if the number is .51 or higher) when there are 0 places after the decimal point?
There are more notes on the worksheet to explain this further.
Thanks for any input.