create a formula that performs a calculation when a # is entered
Posted by Elizabeth B on January 29, 2002 8:50 AM
Hello! I download a program that shows the percent of totals and I would like to change that number to the exact opposite. I'd like a formula that converts that number within its own cell. I can do it in an adjacent cell, but can't seem to nail it within it's own. Example: cell b4 reads "25%". Change it to 75%, right in b4. cell b5 reads 15%. Change to 85%. And so on. I've been doing this: cell b4 reads 25%. formula in b5: =SUM(1-b4).
Posted by Mark W. on January 29, 2002 9:00 AM
Elizabeth, you can enter either a formula or a
value into a cell -- not both. Futhermore, a
formula cannot employ a reference to itself --
that'll produce circular reference error.
However, you can transform your values with a
series of Excel commands. Here are the steps:
1. Enter a 1 into an unused worksheet cell.
2. Copy the value that was just entered.
3. Select your values to be converted and
perform a Paste | Special... Values Subtract
operation.
4. Enter -1 into the cell used at Step 1.
5. Copy the value that was just entered.
6. Select your values to be converted and
perform a Paste | Special... Values Multiply
operation.
7. Reapply a Percentage format.
