Medium function does not work

Asked

Viewed 384 times

3

Hello, I created several random numbers with ALEATÓRIOENTRE() and I awarded them with the CONCAT() I copied these values and pasted them in another part of the spreadsheet. I was trying to media with MÉDIA() However it returns me to no value, only when I select the values and give enter that it starts reading that value. How to fix it? Thanks.Planilha

Also follow the link to view the spreadsheet in excel online: https://1drv.ms/x/s! At0-rSrOjtEyiWEx_Bcw2I3ogE8o

excel
  • 2

    Hello, welcome to SOPT. Your question is not very clear. I suggest to put at least one image of the screen and also the full formula of the function call média, Otherwise someone will hardly be able to help you. This function works well, and you are probably making a mistake. If you haven’t done it yet, do the [tour] and read [Ask].

  • I added print and a pro excel link online :)

  • Okay, now yes. I withdrew the vote to close and answered the question.

1 answer

4


Your problem is that you are manipulating hours as text strings (since you are using the function concat for "merge" hour and minutes). As it makes no sense to calculate the text average (the function média necessarily expects numerical values), it generates error.

What you should have done is use the numeric time format. Excel treats hours as 24-hour decimal values (i.e., 00:00:00 is 0,0, 12:00:00 is 0,5, 24:00:00 is 1,0, 48:00:00 is 2,0 and so on). So you can do the "join" without using the function concat with the following formula (for the calculation of the first column):

=(G8+(G9/60))/24

This formula sums numerically the value of the hour (column G8) with the minutes in decimal (that is, column G9 divided by 60, to give the fraction of hour), and there divides everything by 24 to calculate the value of the decimal hour expected by Excel.

Note that the value of the cell where this formula will be placed will contain a number (and not a text string!). For example, in the image below, cell G18, which displays 01:46, actually contains 0,07. Therefore, the value of this cell can be used to compute a normal average.

By the way, if you wear the mask [hh]:mm in cell formatting, you can display hours above 24 (as 28:42), something that may be useful in displaying accumulated time load, for example.

The result is that the average works as shown below:

inserir a descrição da imagem aqui

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.