Sometimes we create a form in Google Forms or any other application and we want to perform calculations based on the answers.
For example, calculating time remaining until a certain date or checking if the name filled in the form exists in the database.
The problem - when we write a formula in Google Sheets and receive a new submission, the new answer received on a new line "overwrites" the formula we wrote.
There are two possible not complicated solutions, in the following example I will show one of them.
We are holding an event and want to send an arrival confirmation form with the number of participants,
In the form we created, there is a question about how many participants will come?
This is how the answer file will look:
Next to each number of participants we want to calculate a cost per order.
Let's say that for each participant the cost is 117 and we want to write the formula in column C
=B2*117
If we write the formula and drag it along the entire column, as mentioned, the formula will be deleted upon receiving each new answer.
the solution:
Writing one array formula in the first cell that will apply on all the following rows and we'll see the result next to any number of participants without it being deleted.
It is enough to write this formula in one cell (C2) and get the results over all the column:
=ArrayFormula(B2:B20*117)
What we actually wrote is to multiply the entire range B2:B20 by 117 and wrapped the entire formula in "ARRAYFORMULA".
The result is correct, but not very aesthetic because we get zeros in the rows without an answer.
For this purpose, we will write a condition that will contain the formula only if there is an answer in the same line:
=ArrayFormula(if(B2:B20>0,B2:B20*117,""))
The formula says if in the range B2:B20 there are results greater than zero (which means a submission was received) then it will contain the multiplication:
the number of participants times 117 otherwise it will return an empty cell.
Of course, the same principle can be applied on any function or calculation.
Good Luck.
Комментарии