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.

Improve this question
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
    Commented May 8, 2021 at 4:00

1 Answer 1

Reset to default
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)

Improve this answer
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
    Commented May 9, 2021 at 16:12
  • Perhaps you can just use average(C34:C40) where you need the average weight.
    – doubleunary
    Commented May 9, 2021 at 20:51
  • Yes it is sorry, I have accepted your reply as the answer :)
    – Koach
    Commented May 9, 2021 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
    Commented May 13, 2021 at 12:27
  • I do not think that is relevant in this thread. Please post a new question.
    – doubleunary
    Commented May 13, 2021 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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