Google Sheets Formulas – Displaying Results of Formulas in the Same Cell

formulasgoogle sheets

I have a weekly tracking sheet which I'm trying to get the result of 2 averages next to each other in the same cell.

Column B has a number value in each cell for each day of a week
Column G has a number value in each cell for each day of a week
In a cell in column A I want to display the averages of each of the cells from these columns for the last 7 days next to each other with a label.

The formula I have so far in the cell I want to display is:

=AVERAGE(C34:C40) &AVERAGE(G34:G40)

I've got it to display the values but all in a continuous string and the first average result has lots of decimal places after even though when it is in a cell by itself it shows as 1 which is correct, I can't seem to change this?

I also want to add a label to each of the results with the final result in the single cell being:

Weight:result1 Calories:result2

Hope that makes sense if someone can suggest what I need to tweak to get the right display that would be great.

Best Answer

You can use round() to choose how many decimals to show, and the & operator to concatenate text strings and numbers into one text string, like this:

="Weight: " & round(average(C34:C40), 2) & " Calories: " & round(average(G34:G40), 2)

Related Solutions

Google-sheets – Spreadsheet to calculate weekly increase

Your calculation of "This time" / "Last time" expressed as a percentage (by the formatting) is essentially correct, except it isn't working out the increase.

So if it was 10 minutes yesterday, and 10 minutes today, that means you did 100% of the time you did yesterday. This is what you are calculating now.

So (Today / Yesterday) - 1 will give you the increase (when expressed as a percentage). The subtracting 1 removes the original amount.

For example, if it was 10 minutes yesterday, and 11 minutes today, then that is

11 / 10 = 1.1   
1.1 - 1 = .1
.1 * 100 = 10%

If you did 9 minutes today and ten yesterday:

9 / 10 = .9 - 1 = -.1 * 100 = -10%

Google Sheets – Dividing Rows Automatically with a Stationary Cell

Short answer

Try

=IFERROR(OFFSET(E1,0,count(E1:I1)-1),0)/J1

Explanation

The desired calculation could be done by using formulas. As the scripts run on Google Servers, if you are able to do something with formulas try them first as usually they will do calculations faster than a script that does the same.

It's worth to say that you could create custom functions and do some automations by using Google Apps Script¹.

¹: Extending Google Sheets