| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 46960 | 2004-07-11 02:23:00 | Rounding currency | Earnie (3226) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 251462 | 2004-07-11 02:23:00 | Can anyone tell me the formula or function needed to round a $ amount in a Works S/Sheet to the nearest 5 or 10 cents, I know how to set to 1 decimal place but need to be able to show amount as eg: $10.20, not $10.2 when the actual amount that has been calculated by a formula according to a specific set of values comes out to $10.27. | Earnie (3226) | ||
| 251463 | 2004-07-11 03:21:00 | It's frustrating when you post a query and everyone seems to be ignoring you. Rest assured there will be quite a few posters trying to solve for you. I've had a go so far without success |
bonzo29 (2348) | ||
| 251464 | 2004-07-11 03:33:00 | Can you not set the currency format for the result cell? That should give you two digits after the decimal point. Try "round excel" and "currency format excel" to Google. That's how I learned this much about Excel. :D | Graham L (2) | ||
| 251465 | 2004-07-11 03:36:00 | Hi Earnie I don't use MS Works but the way to do it (in the absence of a specific Works formula - I'm sure someone else here will tell you if there is a better way of doing it) will be to mulitply the original number by 20 and add 0.5, chop off (or truncate, trunc?) the decimal places (like round to 0 but not rounding, more like fix or in Excel there is a trunc function), and then divide by 20 again. This will give you your original number rounded to the nearest 5 cents, e.g. 10.27 becomes 10.25 and 10.28 become 10.30. Then use the format options to display the result as currency with 2 decimal places. HTH Andrew |
andrew93 (249) | ||
| 251466 | 2004-07-11 04:29:00 | Thanks andrew93, I will give that a try and keep a watch for any other suggestions. To Graham L, thanks for your reply, I am not able to use excel in this as the computer the work is to be done on is at a sports club and only has works. Thanks again. |
Earnie (3226) | ||
| 251467 | 2004-07-11 05:19:00 | Hi This works, but takes a bit to set up, and you will need to consider which cells to use, for printing purposes. cell g1 enter .00 g2 .01 g3 .02 etc all the way to .99 cell h1 enter .00 h2 .00 h3 .00 h4 .05 h5 .05 h6 .05 h7 .05 h8 .10 get the idea? put in H what you want G to round to cell a1 enter the amount you start with (10.27 in your example) cell b1 enter =int(a1,1) cell c1 enter =a1-b1 cell d1 enter =vlookup(c1,g1:h100,1) cell e1 enter =b1+d1 e1 should be your original amount rounded to nearest .05. You will need to format e1 to fixed with 2 decimals. |
wotz (335) | ||
| 251468 | 2004-07-11 05:30:00 | This is probably different to what you've got, but I'll chuck it in anyway. This is for Microsoft Office Excel 2000 and 2002 (XP). Highlight on cell(s), right click > Format Cells, click on 'Number' tab at the top, highlight 'Currency' from the long list of 'Category', then type in '2' where it says decimal place, then OK... .. and your done! I can't believe you can miss this "simple" (?) thing, so I presume MS Excel in MS Works is different to the one in MS Office. My 2 cent worth anyway... |
~~~~~ s y ~~~~~ (2054) | ||
| 251469 | 2004-07-11 05:34:00 | And now for your next trick Sy, the rounding to nearest .05. And yes, Works is different | wotz (335) | ||
| 251470 | 2004-07-11 05:43:00 | Ooppps... didn't read the question properly! :8} I remember reading a thread like this a few years ago that ended in ~trick not yet discovered by PF1 user~. ;-) |
~~~~~ s y ~~~~~ (2054) | ||
| 251471 | 2004-07-11 05:55:00 | Silly me! You don't need a fix or trunc function - you already know how to use the round function so to get it to round to the nearest 5c use this : multiply value by 20 (i.e. $1/5c), round to 0 dp, divide by 20 - no need to add the 0.5 and truncate given that it is the way to round in the absence of a round function (D'Oh!) In Excel it would look like this : =round((A1*20),0)/20 |
andrew93 (249) | ||
| 1 2 | |||||