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

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
  • 22,071