 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007help with currency formula
IF(D18<>"",D18*K18,"") D18 is time expressed as hours in decimal fractions - i.e., 5.5 equals 5 hours and 30 minutes. K18 is a dollar amount expressed as an exact currency value, i.e., $17.50 or $20.00 There are a number of these formulas in the worksheet, and at the bottom of the column the results are summed. What I am trying to do is round each result so that values are limited to $xx.yy instead of the full numerical calculation carried out by the above formula. IOW, I want the result to be $15.25, not $15.2475 I've been unable so far to come up with any use of the ROUND or related functions to deal wth the result in this formula such that the sum will be exactly what one would expect from adding the dollars and cents. In most cases, the sum of these values is off by a penny. It seems to me that the IF condition is throwing me off, but I don't want to eliminate it because I don't want a cell to contain $0.00 if there's no value for D. TIA, Tom  Signatureremove .spoo to reply by email =IF(D18<>"",ROUND(D18*K18,2),"") SignatureRegards, Peo Sjoblom > IF(D18<>"",D18*K18,"") > [quoted text clipped - 24 lines] > > Tom >=IF(D18<>"",ROUND(D18*K18,2),"") Doesn't work. I get the following: "Shaded cells contain formulas that are automatically calculated by Excel. DO NOT enter any information into them" This worksheet was I believe originally shipped with Excel 2000 and uses some sleight of hand I do not understand. I can't unprotect the worksheet because it asks for a password which I do not have. Unprotecting the cells containing the above formula had no effect - the error message still results. Tom Signatureremove .spoo to reply by email I guess you are out of luck then SignatureRegards, Peo Sjoblom >>=IF(D18<>"",ROUND(D18*K18,2),"") > [quoted text clipped - 10 lines] > > Tom >I guess you are out of luck then Actually, I'm not. While I could not modify the formula, I discovered that I could delete the formula that was there, and then add the formula you gave me. This seems to work. Thank you for your assistance! Tom Signatureremove .spoo to reply by email Tom I don't understand how you can unprotect cells containing formulas without being able to unprotect the worksheet. Do you have the original name of the workbook? Or the type of workbook, like Invoice, Expense or similar? Sounds like one of the Spreadsheet Solutions Templates installed with Excel and MS Office. Select one of the cells and Data>Validation. Is that the source of the message? Gord Dibben MS Excel MVP >>=IF(D18<>"",ROUND(D18*K18,2),"") > [quoted text clipped - 10 lines] > >Tom >Tom > [quoted text clipped - 10 lines] > >Is that the source of the message? No. As I told Peo, the problem went away when I deleted the original formula and then added the new one. Excel didn't complain about the new formula. Also, the worksheet wasn't actually protected. I misread a screen and thought that it was. Thanks, Tom Signatureremove .spoo to reply by email Thanks for bringing us up to date. I would still like to know where the message came from. >"Shaded cells contain formulas that are automatically calculated by Excel. >DO NOT enter any information into them" Gord >>Tom >> [quoted text clipped - 21 lines] > >Tom I liked your guess that it was data|validation. And maybe the cell was cleared (edit|clear|all) or something pasted into the cell destroying the data|validation. > Thanks for bringing us up to date. > [quoted text clipped - 30 lines] > > > >Tom SignatureDave Peterson >Thanks for bringing us up to date. > >I would still like to know where the message came from. > >>"Shaded cells contain formulas that are automatically calculated by Excel. >>DO NOT enter any information into them" So would I - but it's only of academic interest now as, for some inscrutable reason, Excel was okay with replacing a formula, but not with editing it. I'm just glad I'm not running Excel 2007 since hearing about that calculation bug... :-) Tom Signatureremove .spoo to reply by email
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.