1

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.

1
  • It would be helpful if you could share an example sheet in your question, but in general, would the CONCATENATE() formula not work? I think this will give you what you want, you can join text and other formulas together into one string. – OnenOnlyWalter May 8 at 4:00
2

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)

7
  • That seemed to work great thank you! Do you know how I can then reference just one of them values in another cell? So just the average weight number? cheers – Koach May 9 at 16:12
  • Perhaps you can just use average(C34:C40) where you need the average weight. – doubleunary May 9 at 20:51
  • Yes it is sorry, I have accepted your reply as the answer :) – Koach May 9 at 20:52
  • Hi, the formula you gave me works great although the calories isn't adding up correct as in that column cells have a '0' in until filled in but the average is taking them in account, how can it modified to exclude values of '0' please? – Koach May 13 at 12:27
  • I do not think that is relevant in this thread. Please post a new question. – doubleunary May 13 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.