help with currency formula

[Tom Hall]

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

 

[Peo Sjoblom]

=IF(D18<>"",ROUND(D18*K18,2),"")


--


Regards,


Peo Sjoblom

 

[Tom Hall]

=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

 

[Peo Sjoblom]

I guess you are out of luck then


--


Regards,


Peo Sjoblom

 

[Gord Dibben]

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

 

[Tom Hall]

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

 

[Tom Hall]

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?

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

 

[Gord Dibben]

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

 

[Dave Peterson]

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.

 

[Tom Hall]

Thanks for bringing us up to date.

I would still like to know where the message came from.

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