Hi, treading some new ground in Excel, and trying to make a formula to either multiply or divide by 4 to complete a budgeting sheet for expenditure uncured either weekly, monthly or annually, based on the value inserted and the data valuation cell!

Any help is much appreciated!

On Monday, 17 September 2018 03:19:12 UTC+5:30, Brian73 wrote:
Hi, treading some new ground in Excel, and trying to make a formula to
either multiply or divide by 4 to complete a budgeting sheet for
expenditure uncured either weekly, monthly or annually, based on the
value inserted and the data valuation cell!

Any help is much appreciated!


+-------------------------------------------------------------------+
|Filename: Capture.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=1059|
+-------------------------------------------------------------------+



--
Brian73

Can you send me a worksheet with some sample data and what you require to be done so I can try and find a solution to the problem? Thanks.
CA Vikram S. Mathur

Let's say you had, in cell A1, a very simple formula, =A2+A3.
You can then apply Data Validation to that cell, to restrict user input, for example to a list.
As long as you apply them in that sequence (formula first, then DV), then Excel will let you do it.
The formula will calculate correctly, even if the result is not an allowable input as far as DV is concerned.
You will then be able to overwrite the formula, using DV.
Try it.

But I would be careful with this.
Partly because, once you've applied DV, it will be tricky to input the formula a second time, or even to change the formula, if you want to do that.
You'd have to take off DV to do that.

I would consider doing this in separate cells - have a formula that gives you one result.
Then have a separate DV cell, for optional user input.
Then have another formula, which says, IF the DV cell is blank, pickup the result of the first formula.
If the DV cell is NOT blank, pick up the value in the DV cell.

I hope I was able to give you some insights.

You can get more help with https://www.o365cloudexperts.com if needed.

Regards,
Jerry