Calculation within a cell.

By 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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Request for Clarification

by PurpleSkys In reply to Clarifications

Why can't it reference another cell?

Collapse -

Unless Excel has some features I'm not aware of

by OurITLady In reply to Calculation within a cell ...

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.

Collapse -

Calculation within a cell.

by Abbaink In reply to Calculation within a cell ...

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.

Collapse -

Reponse To Answer

by PurpleSkys In reply to Calculation within a cell ...

you can hide the referencing cells

Collapse -

Reponse To Answer

by Darryl~ Moderator In reply to Calculation within a cell ...

That just doesn't make sense, if they are entering in the "original" amount it would have to be seen because they have to enter it in a cell.
So what you're saying is that you would like the "client" to enter 200 in cell A1 and as soon as they hit enter the value in A1 would become 240?
That woun't work for a couple reasons, as stated above, if a formula was in cell A1 it would be over wrote by the client typing a value in it & even if you were able to do it, it would be stuck in a never ending loop of adding 20% to the value (200 + 20%.....240 + 20%...288 + 20%...etc).
You could have the client enter values on one sheet and show the calculations on a different sheet......or you could even use 2 different workbooks if desired.

Collapse -

Reponse To Answer

by boxfiddler Moderator In reply to Calculation within a cell ...

Put the reference cell at one of the corners of the spreadsheet. Then format that cell font as white once you've entered the necessary reference. Providing you're printing on white paper.

Collapse -

Not possible

by In reply to Calculation within a cell ...

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.

Collapse -

An Alternative

by a.portman In reply to Calculation within a cell ...

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.

Collapse -

macro and onchange event will do that

by richardreynoldsus In reply to Calculation within a cell ...

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.

Collapse -

There is a manual way ...

by zdnet In reply to Calculation within a cell ...

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 :-)

Related Discussions

Related Forums