Forum Discussion
Can a cell be hard coded to multiply a value upon insertion?
Hi guys, I am new to excel so hope that this is not a dumb question. Eg- So if I insert 10 into B5 can it be coded to give me the multiplication of 4? B5 itself showing 40? Is there a formula for that?
Thank you in advance.
It is possible, but it is not a good idea.
In the first place, it would be error-prone. Let's say you enter 10 and Excel changes it to 40 automatically. So far so good. But if you then accidentally edit the cell and press Enter or Tab even without changing anything, Excel would change it to 160, and the next time to 640. So it would be hard to be sure what the intended value was.
In the second place, it would either require rather tricky formulas (also error-prone), or VBA code, requiring users to allow macros - this can be a problem in some environments.
As an alternative, I would recommend using a separate cell (or column) for the multiple.
In cell C5, enter the formula
=IF(B5="", "", 4*B5)
C5 will remain blank until a value is entered in B5, then display the 4x multiple.
You can use C5 in further calculations that rely on the multiple.
The formula can be filled/copied upwards and downwards if necessary.
- Hi, thank you so much. Your solution works perfectly for my context.
