Software

Ask something
Question Locked

Calculation within a cell.

0 helpful
11 replies

by abbaink

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

11 replies

by 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.

by 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.

by darryl~

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.

by boxfiddler

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.

by Member 21191736

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.

by 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.

by 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.

by zdnetaaa2

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

This discussion is closed to new replies.