• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

=CHOICE() as replacement for IF

Henk

New Member
Hi,


I have a sheet that requires multiple nested IF statements in calculations and was thinking of using CHOICE to drive selection of formula. Here is an example of what I'd like to do. A1:B10 contain values, in C1:C10 I'd like to put a =Choice() formula that refers to formulas that are written out in e1:e3 (Ex: e1: A1+B1, e2: A1-B1, e3: A1*B1). I'd like to add a column D1:D10, with 1,2 or 3 to drive the choice of formula. Is there a way that I only need to write out the formula once in those 3 cells e1:e3, and apply my choice to the result cell in C without using VBA?


Thx.
 
Hi Chandoo, thanks for your reply and love your site.


My workbook is pretty large - ~ 4000 row, 12 sheets. Ideally, I'd like to be able to control which formula is used, and have an easy way to change those formulas in entire workbook, by just referencing those 3 written out formula cells - it's a mainly a matter of error control. The formulas are fairly complicated (~3 lines in the standard input box)and I found that it's easy to make errors. We are sharing this model with a wide group that is not very excel savvy and I need to be able to instill some level of confidence in the output, which is hard to do when all these cells are complex big formulas.


The referencing of these formula cells would be an elegant easy solution, that is easy to update


Thx
 
Henk

I know you said no VBA, but User Defined functions are ideal for what you are trying to do

The advantage I mostly find is that they are auditable, very easy to see where they are and easy to check what they do

You can lock them down so that no one else can change them

They are not that difficult to learn.
 
Henk.. I would write a small UDF as Hui suggested. something like this is good enough (I have been using it for awhile btw).


Function runFormula(fromThisCell As Range) As Variant

'runs the formula specfied in the input cell using Application.Evaluate

runFormula = fromThisCell.Value

On Error Resume Next

runFormula = Application.Evaluate(fromThisCell.Value)

End Function


Now, use the runFormula(1), runFormula(2)..., runFormula(10) in your choice.
 
@Hui - Thanks I think I'll give that a go. I've been playing around before with UDFs but couldn't figure out how to write out the formula in a cell and call it from there.


@ Chandoo - I'm going to have to figure out how to deal with relative references in the formulas, but this might work! Thanks a bunch.
 
Chandoo,


Can't seem to get the application.evaluate to work - it always gives me the same result (from original references). I did have some luck with a combination of below. (found both of these online somewhere) But usesameas always refers to latest active cell, so does not really work dynamically. I've also not been able to combine these. Ideas?


Function UseSameAs(Cell As Range)

'-- Use the same Formula as used in the referenced cell

'-- http://mvps.org/dmcritchie/excel/formula.htm#usesameas 2005-09-03 .excel

Application.Volatile

If Cell.HasFormula Then

UseSameAs = Application.Caller.Parent.Evaluate(Cell.Formula)

Else '-- needed if constant looks like a cell address

UseSameAs = Cell.Value

End If

End Function


Function Eval(Ref As String)

Application.Volatile

Eval = Evaluate(Ref)

End Function
 
hmm... I didnt realize that earlier...Then we should go back to Hui's suggestion and define UDFs for each formula you want to calculate. then use those UDFs in the choice along with parameters.

that way you have full control over the formula thru udf and it is easy to change if you want to.
 
Henk


a User Defined Function can be as simple as


Function Add(c1 As Variant, c2 As Variant)

Add = c1 + c2

End Function


where you use the formula


=+Add(A1,A2)


or as complex as you want to make it...
 
Back
Top