Forum Discussion

NikithCharles's avatar
NikithCharles
Copper Contributor
Sep 13, 2021

Using Text and Average functions together

Hello,

I'm quite new at Excel and am trying my hand at functions. Could someone help me with this?

How do I frame a function in a way that would give the result "The average= XXX", where "The Average=" is a text string and XXX is a formula for the average of numbers in a column?  Also, I'd like the average formula to be dynamic, meaning, if I select 5 numbers, it gives me the average of 5 numbers. In a later instance, if I select 10 numbers, it should give me the average of 10 numbers. 

Thanks in advance.

excel
Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 13, 2021

    NikithCharles 

    One option is to concatenate the text and the AVERAGE function:

    ="The average = "&AVERAGE(A1:A10)

    Another option is to use

    =AVERAGE(A1:A10)

    and to apply the following custom number format to the cell with the formula:

    "The average ="General

    or

    "The average = "0.00

    AVERAGE ignores blank cells and cells with text values, so if you don't know whether you'll fill 5 or 10 or 25 cells, you can safely use AVERAGE(A1:A100)

Resources