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.
Also follow the link to view the spreadsheet in excel online: https://1drv.ms/x/s! At0-rSrOjtEyiWEx_Bcw2I3ogE8o
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]:mmin cell formatting, you can display hours above24(as28:42), something that may be useful in displaying accumulated time load, for example.
The result is that the average works as shown below:
Browser other questions tagged excel
You are not signed in. Login or sign up in order to post.
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].– Luiz Vieira
I added print and a pro excel link online :)
– mrlucasrib
Okay, now yes. I withdrew the vote to close and answered the question.
– Luiz Vieira