Multiply/Divide A Range Cells By The Same Number

[AtomParticipant]

Hi,

Excel provides a quick way to apply a mathematical operation on a range of cells. You can use the Paste Special function to multiply a range of cells by a number. See screenshot below. I love this feature so much and use it very heavily in Excel.  I know in Datagraph we can use an expression column to do math for columns, but for rows, we need to transpose to columns first, calculate and then transpose them back. It’s not efficient. Is there an efficient way to do the same thing in Datagraph? Thanks.

Reference: https://www.extendoffice.com/documents/excel/697-multiply-excel-cells-by-a-number.html

[dgteamModerator]

We released a new feature in the latest Beta version aimed at making operations across multiple columns easier.  Would love to have you try it out and see if this helps.  Here is how it works:

1 – First, place the columns that you want to apply the mathematical operation in the same data group.

(Quick YouTube Demo on Data Groups: https://youtu.be/Md4PPges5wY)

 

2 – Click the gear menu on the group.

 

3 – The operations that are in the beta now are to ‘Normalize’ (from 0 to 1) and to ‘Scale by’ a column. If you choose scale by a column, you also pick what column to use.

 

4 – When you select one of these options, a new group is created that contains expression columns that perform the operation.

Here is this example for Normalize:

Here is this example for Scaled by Col 1:

We plan to include other options like scale by a variable.  For now, you could scale by a single number by first creating a column with that number in the same group, and then select that column to scale by.  For example, here all three columns are divided by 2.

Does this provide a mechanism for you to get the same result as the action in excel?

We appreciate feedback on this new option before we release it in the next version.

[sandriftParticipant]

I can’t respond on behalf of the OP, but this is a welcome feature for me — I’ve been playing with it all morning.

Being able to set the x-axis range over which the normalization occurs would also be very helpful (e.g., using a mask).  Also, being able to scale all plots to a specific value at a given x-axis position would be great.  I can provide example data for this use case if it would be of help.

Thanks.

[AtomParticipant]

Thanks. Tried this feature, it helps.

I tried to copy the results back to their original. It shows “Circular” error. Is it possible to apply the scaled results back to original columns?

[dgteamModerator]

@sandrift – We have to think about how to implement the mask.  If you have an example data set for scaling for an x-axis position you could email us that would be very helpful.

@Atom  – To paste over the data, highlight the new columns. Then select  “Edit > Copy as Text” instead of a simple Copy.

Next, highlight the columns you want to paste over and select Edit > Paste.

This will overwrite the data with the values, instead of the pasting the expression columns and causing the circular reference.

FYI – In case you are asking this because you have commands based on the first columns that you want to update, one handy trick is to drag and drop the group Normalized onto any commands, or groups of commands, that have columns with the same name.  Then all the column references in the commands will be updated, without you having to change the data.

For example, here is one command based on the original columns.  Click and Drag the group from the column list to the command.

Drop the group onto the command to update all the references to the new group.

This update mechanism is all based on the columns names.  If you renamed the columns, no changes would be made when you dropped the data group on the command.

Either way, this is a handy trick whether you need it now or not.  If you have multiple commands to update, place them in a group and drag onto the group.

[Author]

Posts