Apply different formulas depending on cell value

[AxelU]

Hi,

I am looking for a way to dynamically change which formula is used depending on the value in another cell, example:

[TABLE="width: 216"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]If value is between[/TD]
[TD]Use formula[/TD]
[/TR]
[TR]
[TD]0-40[/TD]
[TD]ROUND[/TD]
[/TR]
[TR]
[TD]40-100[/TD]
[TD]ROUNDUP[/TD]
[/TR]
[TR]
[TD]100-1000[/TD]
[TD]ROUND[/TD]
[/TR]
[TR]
[TD]1000-10000[/TD]
[TD]ROUNDDOWN
[/TD]
[/TR]
</tbody>[/TABLE]

I am looking for a solution that does not require endless if-statements. Rather looking for a solution where one specifies the formula to be used in a range which is the applied through an index-match-statement or similar. I have tried using the INDIRECT function but it does not seem to convert the text into different formulas.

Any help is well appreciated!

/Axel

 

[Joe4]

I can think of two ways of doing it:
1. Use a Nested IF statement in Excel (it is not endless, you only have 4 conditions, so it shouldn't be that big of a deal)
2. Create your own User Defined Function to do it (using VBA)

 

[Special-K99]

You need to redefine your limits
From your description

0-40 ROUND
40-100 ROUNDUP

So if the value is exactly 40
you want BOTH ROUND AND ROUNDUP

I bet you dont.

Take another look at (all) your limits

 

[AxelU]

Thank you for your replies.

The attached was just an example, please ignore the intervalls and number of conditions. What I want to know is if it is possible to fetch a different formula for a cell, deepening on another cells value without using if-statements.

For example:

If B4 is over 10 i want to ROUNDDOWN the value
if B4 is below 10 I want to ROUNDUP the value

There will be numerous variations of this why I do not want to use if-statements. The best would be some kind of list of formulas that could be concated to cell references.

Thanks in advance!

Br,
Axel

 

[mikerickson]

The attached was just an example, please ignore the intervalls and number of conditions. What I want to know is if it is possible to fetch a different formula for a cell, deepening on another cells value without using if-statements.

Nested IF would be the easiest for my thinking, but if you really don't want IF for some reason, you could use something like

=CHOOSE(MATCH(C2,{0,41,101,1001,10001}),ROUND(A1,0),ROUNDUP(A1,0),ROUND(A1,0),ROUNDDOWN(A1,0))

 

[Joe4]

The UDF option may look something like this:

Function MyRound(myValue As Double) As Double

    Application.Volatile
    
    Select Case myValue
        Case Is < 40
            MyRound = Round(myValue, 0)
        Case Is < 100
            MyRound = Application.WorksheetFunction.RoundUp(myValue, 0)
        Case Is < 1000
            MyRound = Round(myValue, 0)
        Case Is < 10000
            MyRound = Application.WorksheetFunction.RoundDown(myValue, 0)
        Case Else
            MyRound = myValue
    End Select
                
End Function

Then just use it like any other Excel function on your sheet, i.e.
=MyRound(A1)