Questions

Calculation within a cell.

Tags:
+
0 Votes
Locked

Calculation within a cell.

Abbaink
How do I create in Excel a cell that will automatically add 20% to any amount input into it without requiring a separate source cell for it to refer to? In other words how do I create a cell that if I input $100 it will automatically display the amount $120?

Clarifications

PurpleSkys Moderator

Why can't it reference another cell?

  • +
    0 Votes
    OurITLady

    you'd have to reference another cell - if you type anything into the cell containing the formula surely you'd overwrite the formula? I admit I'm not an expert, but I really can't think of any way it would be possible to enter the information into the same place as the calculation and have it not wipe the calculation.

    +
    0 Votes
    Abbaink

    The layout of the spread sheet will not work if I have to have referencing cells.
    In other words, I do not want the first amount to be seen at all. I would like to have the client put an amount in and have the 20% added to that amount automatically.

    +
    0 Votes
    gresher

    As all the others above have said, as soon you type something into the cell your calculation would be overwritten. The only way I can see off achieving the same result would be to use Visual Basic to replace the value after being input by the user or, as the others have said, you need to have a reference cell.

    +
    0 Votes
    a.portman

    No I have no way of entering 100 in A1 and having it become 120. Why do you need this and do not want to display both numbers?

    Anyway, have A1 be the user input cell. 100 goes in A1. B1 gets A1*1.2 and the B column gets hidden. You now have the user input and the 20% markup you can do math with.

    How were you planning on explaining to the end user that even though they entered 100, 120 is the right amount? Even if you avoid the overwrites and do loops, you still need to deal with I typed 100 and I would like to see 100.

    +
    0 Votes
    richardreynoldsus

    catch the change, update the number, for safety put the last onchange value in a hidden cell near the end of your sheet. ex BZ:16650 before updating the specific cell.

    +
    0 Votes
    zdnet

    There is a "manual" way to do this... instead of entering "100" into your cell and using some other cell to multiply by 1.2, put =100*1.2 into your cell. That way you don't need a (hidden) separate cell. You do now need to be careful to only change the "100" and not the other part of the formula...

    I use this quite a lot to be able to calculate "plus-tax" and "plus-margin" values without needing additional cells or doing calculations on paper :-)

  • +
    0 Votes
    OurITLady

    you'd have to reference another cell - if you type anything into the cell containing the formula surely you'd overwrite the formula? I admit I'm not an expert, but I really can't think of any way it would be possible to enter the information into the same place as the calculation and have it not wipe the calculation.

    +
    0 Votes
    Abbaink

    The layout of the spread sheet will not work if I have to have referencing cells.
    In other words, I do not want the first amount to be seen at all. I would like to have the client put an amount in and have the 20% added to that amount automatically.

    +
    0 Votes
    gresher

    As all the others above have said, as soon you type something into the cell your calculation would be overwritten. The only way I can see off achieving the same result would be to use Visual Basic to replace the value after being input by the user or, as the others have said, you need to have a reference cell.

    +
    0 Votes
    a.portman

    No I have no way of entering 100 in A1 and having it become 120. Why do you need this and do not want to display both numbers?

    Anyway, have A1 be the user input cell. 100 goes in A1. B1 gets A1*1.2 and the B column gets hidden. You now have the user input and the 20% markup you can do math with.

    How were you planning on explaining to the end user that even though they entered 100, 120 is the right amount? Even if you avoid the overwrites and do loops, you still need to deal with I typed 100 and I would like to see 100.

    +
    0 Votes
    richardreynoldsus

    catch the change, update the number, for safety put the last onchange value in a hidden cell near the end of your sheet. ex BZ:16650 before updating the specific cell.

    +
    0 Votes
    zdnet

    There is a "manual" way to do this... instead of entering "100" into your cell and using some other cell to multiply by 1.2, put =100*1.2 into your cell. That way you don't need a (hidden) separate cell. You do now need to be careful to only change the "100" and not the other part of the formula...

    I use this quite a lot to be able to calculate "plus-tax" and "plus-margin" values without needing additional cells or doing calculations on paper :-)