Ask Your Question
0

How can I quickly add MROUND to a bunch of cells?

asked 2019-09-26 23:25:37 +0200

trireme32 gravatar image

I'm using a weightlifting spreadsheet that calculates progression based on percentages. The only problem is that none of the formulas were set to round to 5, which is a necessity. It would take me forever and a day to change every formula to include MROUND(original formula, 5).

I've read the Regular Expressions Find and Replace help page, but it's over my head.

Is there a simple way I can get this done?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2019-09-27 01:32:08 +0200

Opaque gravatar image

updated 2019-09-27 01:38:14 +0200

Hello,

select the cells you want to change and got to use Edit -> Find & Replace

Set field Find to: ^(=)(.*)
Set field Replace to: $1MROUND($2,5)
Set Other options Current selection only
Set Other options Regular Expression
Set Search in to Formula

Finally click button Replace All

image description

Note Notation using US locale (-> function parameter delimiter is comma [,])

Tested using:

Version: 6.3.2.2, Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

Hope that helps.

edit flag offensive delete link more

Comments

Note Notation using US locale (-> function parameter delimiter is comma [,])

Using a universally accepted ; might possibly be better and more universal suggestion?

A nitpick: it's unnecessary to capture a known character and then replace with its reference - it's easier to replace ^=(.*) with =MROUND($1;5) ;-)

Mike Kaganski gravatar imageMike Kaganski ( 2019-09-27 08:39:07 +0200 )edit

@Mike Kaganski: Isn't a nitpick - You are absolutely right. It was too late for me, and I even don't have any clue why I fought with that some time: Related to ; - that's new for me. Thanks and of course it's the better suggestion.

Opaque gravatar imageOpaque ( 2019-09-27 14:04:25 +0200 )edit

This worked perfectly thank you!!

trireme32 gravatar imagetrireme32 ( 2019-09-27 16:04:49 +0200 )edit
1

answered 2019-09-27 08:13:01 +0200

keme gravatar image

updated 2019-09-27 08:52:53 +0200

Modifying formulas by regex carries potential for error, but in this case the regex substitution suggested by Opaque should work.

If your formulas are part of a tabular setup (and essentially identical), the usual way to deal with this is copy-paste:

  • Edit one formula and check that it works as intended.
  • When satisfied, copy that cell and paste into the other cells where it is needed.
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-09-26 23:25:37 +0200

Seen: 58 times

Last updated: Sep 27 '19