1

I have this formula

=ROUNDUP(D12/(D6-D5),IFERROR(VLOOKUP(G9,reference!$C$2:$D$7,2,0),100))

and I want to change the ROUNDUP portion based on text from a separate cell (say, F7, where the text can be ROUNDDOWN or ROUND ).

I tried using the SUBSTITUTE() and REPLACE() functions. These are a couple of my attempts:

=REPLACE("=ROUNDUP(…)",2,7,F7)*

=REPLACE(ROUNDUP(…),2,7,F7)

*This version ended up creating the right formula, but it shows as text, not as a formula. I can paste it in another cell and it works. So I tried adding *1, in this formula but it still didn't work.

A workaround is the use of nested IF , but that will create far too many lines so I was hoping for another option.

Improve this question
7
  • What value/Function U've written in F7 ?
    – Rajesh Sinha
    Commented Jul 18, 2018 at 6:16
  • 1
    Unfortunately, you can't update a function or formula by text. Only exception is the references which can be altered by the INDIRECT function.
    – FezzikMontoya
    Commented Jul 18, 2018 at 6:37
  • I added the function I want for F7 , @Rajesh. @FezzikMontoya, that's what I was afraid of.
    – rose
    Commented Jul 18, 2018 at 8:07
  • Actually, there's a limited capability to do this kind of thing with EVALUATE. See superuser.com/a/774135/364367
    – fixer1234
    Commented Jul 18, 2018 at 8:22
  • @rose, Y don't U use Find & Replace by choosing Formula Option, it's easier. And if you replace the formula with Text then how it will work !!
    – Rajesh Sinha
    Commented Jul 18, 2018 at 8:25

1 Answer 1

Reset to default
0

You can use CHOOSE and MATCH functions, still not optimal, but better than IF:

=CHOOSE(MATCH(F7,{ROUND,ROUNDUP,ROUNDDOWN},0),ROUND(D12/(D6-D5),ROUNDUP(D12/(D6-D5),ROUNDDOWN(D12/(D6-D5))

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .