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.

rose
  • 11
  • 2
  • What value/Function U've written in `F7` ? – Rajesh Sinha Jul 18 '18 at 06: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 Jul 18 '18 at 06:37
  • I added the function I want for `F7` , @Rajesh. @FezzikMontoya, that's what I was afraid of. – rose Jul 18 '18 at 08:07
  • Actually, there's a limited capability to do this kind of thing with EVALUATE. See https://superuser.com/a/774135/364367 – fixer1234 Jul 18 '18 at 08: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 Jul 18 '18 at 08:25
  • @rose, I can suggest you MACRO(VBA) will help you to replace any part of the formula Like `Roundup` or `Iferror` or `Vlookup` with the value(function) store in cell. Just confirm are you comfortable with MACRO? – Rajesh Sinha Jul 18 '18 at 09:03
  • i did do macros a couple of years back, but i'd rather not at this point because people here do not know how to code and I want people to be able to edit this file. I can also do the find and replace, but I'd rather have it automatically done. thanks though :) – rose Jul 18 '18 at 10:02

1 Answers1

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))

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73