• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Mileage Calculation

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
 

Attachments

GraH - Guido

Well-Known Member
Custom format to show "0" as "Enter Amount" in blue
Code:
"$"#.##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.
 

Attachments

Peter Bartholomew

Well-Known Member
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 :rolleyes:!

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.
 

Attachments

Top