Apply same formula to every cell

user_3301994 0
Viewed 106

I have a sheet with several numbers, which I want to convert to percentage. These are scores imported from a form, where the maximum score is 5.

According to the bellow image, the cell B2, I applied a basic formula =(3/5)*100% to convert three to 60%.

enter image description here

In order to avoid copy and paste the same formula into all cells, is there any formula to find all the cells and apply its value, divide per 5 and multiple per 100%? I thought something similar to Javascript such as (this/5)*100%.

https://docs.google.com/spreadsheets/d/1ZtqJaXy1pHkjk1sywGfSodo0FcOoLlkou79cZBk-jmU/edit?usp=sharing

formula cell google-sheets
3 Answers

you could treat it as array and on Sheet2 use:

=ARRAYFORMULA(IF(Sheet1!B2:AA="";;(Sheet1!B2:AA/5)*100%))

enter image description here

demo sheet

user_56326290

I've also looked for a solution to this for some time.

I don't think it's possible as a cell can only hold one value and the moment you try to change that value, it will delete the value.

Your best bet is to create a different table on the same sheet that references the specified values, then copy and paste the results (values only).

user_100079580

Just create a second Sheet, then in cell B2 of Sheet2 use the formula =Sheet1!B2/5*100%.

Then copy the formula to all cells of Sheet2.

user_20874780
Related