Mileage Calculation

[Frank Bacchus]

Hi all:

In the attached Excel Sheet, there are a "Mileage" cell, a "Mileage Rate" cell and an "Amount" Cell. I want to use the "Amount" cell for dual purposes. I want to calculate the Mileage * Mileage Rate, placing the results in the "Amount" cell, only if the "Mileage" cell is populated. If the "Mileage" cell is not populated, I want to use the "Amount" cell to record any other amount (see uploaded file). Total Expenses (cell F4) is automatically calculated with the total amounts from the "Amount" cells. Thanks

frank

 

[GraH - Guido]

Custom format to show "0" as "Enter Amount" in blue

"$"#.##0,00_);[Red]("$"#.##0,00);[Blue]"Enter Amount"

(see file, because I use EU settings)
[E9] =IF(D9,D9*$E$7,IF(B9,0,""))

So when the calculation is not required, one can overwrite by filling out an amount.

 

[Peter Bartholomew]

Other solutions would involve VBA or, possibly, a 'smoke and mirrors' approach such as that contained within the attached workbook.
I have converted to a table so that any formula will propagate down if further rows are added.

The danger with having unprotected formulas to be overwritten is that over time they will be deleted and not replaced correctly. Ultimately even the total is overwritten because it isn't right any more. Then, if the user forgets to calculate the total by hand, they will submit a new claim with the old total. You may notice that I am not a great fan of overwriting cells despite the fact that that is precisely what my accountant does with spreadsheets I write for their use !

What the spreadsheet conceals is a hidden column in which the mileage amounts are calculated; these figures enter the total. There is also a very thin column in which the mileage amount is converted to text if the amount column is empty. By using 'Center across Selection' alignment the text will appear to be in the Amount column.