Hello. I am creating an Expense Report worksheet and have created a simple formula that will calculate mileage based on total miles. Below is my worksheet data. A B C D 1 Expense Type Acct. Code Total Miles Amount 2 Airfare 11111 $250.00 3 Mileage 22222 20 $10.00 I am trying to figure out a way to create a conditional formula so that IF Expense Type is "Mileage", data in "Total Miles" must not be blank, and the "Amount" will automatically calculate based on the formula "C3*.50". Additionally, I want to be able to enter an Amount in Column D without overwriting the underlying formula. For example, say in A2 I enter Expense Type "Airfare" and then enter the total amount of $250.00 Column D. Once I enter data in Column D, I overwrite the formula. If I need to go back and change the Expense Type to "Mileage", Column D won't automatically calculate anymore. By typing data into this cell, I have essentially deleted the formula. Is there a way to maintain the underlying conditional formula while at the same allowing me to manually enter data in that cell without losing the formula? Thanks for your help!

0 |

1/16/2010 12:39:01 AM

Use two cells. Say we have a formula in A1: =SUM(A2:A100) Sometimes we would like to override the formula with a fixed value and still have the formula available. Use B1 for the fixed value and in A1: =IF(ISBLANK(B1),SUM(A2:A100),B1) -- Gary''s Student - gsnu200909 "MsBeverlee" wrote: > Hello. I am creating an Expense Report worksheet and have created a simple > formula that will calculate mileage based on total miles. Below is my > worksheet data. > > A B C > D > 1 Expense Type Acct. Code Total Miles Amount > 2 Airfare 11111 > $250.00 > 3 Mileage 22222 20 > $10.00 > > I am trying to figure out a way to create a conditional formula so that IF > Expense Type is "Mileage", data in "Total Miles" must not be blank, and the > "Amount" will automatically calculate based on the formula "C3*.50". > > Additionally, I want to be able to enter an Amount in Column D without > overwriting the underlying formula. For example, say in A2 I enter Expense > Type "Airfare" and then enter the total amount of $250.00 Column D. Once I > enter data in Column D, I overwrite the formula. If I need to go back and > change the Expense Type to "Mileage", Column D won't automatically calculate > anymore. By typing data into this cell, I have essentially deleted the > formula. Is there a way to maintain the underlying conditional formula while > at the same allowing me to manually enter data in that cell without losing > the formula? > > Thanks for your help!

0 |

1/16/2010 2:09:01 PM