DIVIDE BY NON-EMPTY CELLS ONLY
Appolonie
-
tontong Posted messages 2575 Registration date Status Membre Last intervention -
tontong Posted messages 2575 Registration date Status Membre Last intervention -
Hello,
I am currently calculating various ratios for the internal analysis of my department.
I have created an "attendance simulation" in Excel so that each operator can log the time they spend on different tasks every day.
For example: 01/03/2010 02/03/2010 03/03/2010 04/05/2010
consultations 40min ...
phone negotiation 20 min
contract management 14 min ...
Each data entry in minutes is then converted in an adjacent cell into hundredths of hours.
At the end of the rows, I calculate, for the filled month, a certain number of ratios that reflect averages, which are, by this title, composed of one element divided by the number of working days...
For example: the average time per day for order entry, in units of hundredths of hours = sum of the filled cells for the order entry task in the month / 23 working days in the month.
However, as long as the month is not finished, I cannot do anything with these stats because I divide by 23 (working days) and not by the actual number of days logged, so all my averages are skewed. My cell automatically divides the first 3 filled days by 23, and I end up with very, very, very low averages...
My question is the following: Is there a formula that allows me to divide only by the number of filled cells... a formula that would update my averages automatically as the operators log their tasks every day. (for example, that the "23" be replaced by "number of non-empty cells"... that’s the idea)
Thank you in advance for your help, I am doing this attendance module for my end-of-year thesis... but I am just starting with EXCEL
Looking forward to your ideas and comments,
Appolonie.
I am currently calculating various ratios for the internal analysis of my department.
I have created an "attendance simulation" in Excel so that each operator can log the time they spend on different tasks every day.
For example: 01/03/2010 02/03/2010 03/03/2010 04/05/2010
consultations 40min ...
phone negotiation 20 min
contract management 14 min ...
Each data entry in minutes is then converted in an adjacent cell into hundredths of hours.
At the end of the rows, I calculate, for the filled month, a certain number of ratios that reflect averages, which are, by this title, composed of one element divided by the number of working days...
For example: the average time per day for order entry, in units of hundredths of hours = sum of the filled cells for the order entry task in the month / 23 working days in the month.
However, as long as the month is not finished, I cannot do anything with these stats because I divide by 23 (working days) and not by the actual number of days logged, so all my averages are skewed. My cell automatically divides the first 3 filled days by 23, and I end up with very, very, very low averages...
My question is the following: Is there a formula that allows me to divide only by the number of filled cells... a formula that would update my averages automatically as the operators log their tasks every day. (for example, that the "23" be replaced by "number of non-empty cells"... that’s the idea)
Thank you in advance for your help, I am doing this attendance module for my end-of-year thesis... but I am just starting with EXCEL
Looking forward to your ideas and comments,
Appolonie.
Configuration: Windows XP / Internet Explorer 7.0
6 réponses
Hello
to count non-empty cells in a column:
=COUNTIF(RANGE,"<>")
only works if there are no formulas in the cells.
If cells contain formulas that yield 0: when they are not documented, the formula is:
=COUNTIF(RANGE,">0")
Best regards
P.S.: so the 1st formula should apply to the filling column, not the one that calculates the hundredths.
--
Let's ask ourselves if we are not alone in understanding what we are explaining?
to count non-empty cells in a column:
=COUNTIF(RANGE,"<>")
only works if there are no formulas in the cells.
If cells contain formulas that yield 0: when they are not documented, the formula is:
=COUNTIF(RANGE,">0")
Best regards
P.S.: so the 1st formula should apply to the filling column, not the one that calculates the hundredths.
--
Let's ask ourselves if we are not alone in understanding what we are explaining?
Good evening,
I think you're stuck because you didn't know that Excel supports time format, which is why it's going to the hundredth.
Example:
In A1 -> 1:00 -> for 1 hour
In A2 -> 0:30 -> for 30 min
In A3 -> =A1+A2 -> by writing it like this, the software sums the hours and minutes because you've written it in time format (result 1:30 for 1h30).
So, you would be able to average the hours spent per day without using tricks -> =AVERAGE(A1:A3)
Be careful, pay attention to your cell format: right click/format/time!
Indeed, this will require you to rethink your spreadsheet, (but so much simpler). Otherwise, Ms. Vaucluse's formula works very well.
Best regards,
Migou
I think you're stuck because you didn't know that Excel supports time format, which is why it's going to the hundredth.
Example:
In A1 -> 1:00 -> for 1 hour
In A2 -> 0:30 -> for 30 min
In A3 -> =A1+A2 -> by writing it like this, the software sums the hours and minutes because you've written it in time format (result 1:30 for 1h30).
So, you would be able to average the hours spent per day without using tricks -> =AVERAGE(A1:A3)
Be careful, pay attention to your cell format: right click/format/time!
Indeed, this will require you to rethink your spreadsheet, (but so much simpler). Otherwise, Ms. Vaucluse's formula works very well.
Best regards,
Migou
Thank you very much for your answers! Indeed, it seems much simpler. I was familiar with the hour:minute format.. but I can't get it to work, so I opted for the other solution.
The initial problem is as follows for the hour format:
For example, if I enter in my cells:
in A1: 0.30 (for 0:30)
in A2: 1.00 (for 1:00)
in A3: 0.45 (for 0:45)
Select A1A2A3 -> right-click -> cell format -> time -> type 13:30
this format should correspond to hh:mm
However, when I validate this choice, strange times replace my previously entered data, times that do not correspond to anything, for example for the values given above, it transforms into:
A1: 7:12
A2: 0:00
A3: 10:48
And the same result if I set the time format before entering the values in the cells..
Is there a particular setting or formatting to perform to obtain correct values??
Thank you
Appolonie
The initial problem is as follows for the hour format:
For example, if I enter in my cells:
in A1: 0.30 (for 0:30)
in A2: 1.00 (for 1:00)
in A3: 0.45 (for 0:45)
Select A1A2A3 -> right-click -> cell format -> time -> type 13:30
this format should correspond to hh:mm
However, when I validate this choice, strange times replace my previously entered data, times that do not correspond to anything, for example for the values given above, it transforms into:
A1: 7:12
A2: 0:00
A3: 10:48
And the same result if I set the time format before entering the values in the cells..
Is there a particular setting or formatting to perform to obtain correct values??
Thank you
Appolonie
Good evening Migou
I agree with your conclusions, except perhaps if I may say so, about the "Mlle Vaucluse." If you had a photo, you would be surprised!
Apart from that, all your interventions are interesting and you should consider signing up as a member. You could then take a look at my profile, which in the end isn't too feminine.
I give you a pinch, you will understand later if you follow my advice, why I don't give you a kiss.
Best regards
--
Let’s ask ourselves if we are not the only ones understanding what we explain?
I agree with your conclusions, except perhaps if I may say so, about the "Mlle Vaucluse." If you had a photo, you would be surprised!
Apart from that, all your interventions are interesting and you should consider signing up as a member. You could then take a look at my profile, which in the end isn't too feminine.
I give you a pinch, you will understand later if you follow my advice, why I don't give you a kiss.
Best regards
--
Let’s ask ourselves if we are not the only ones understanding what we explain?
Aside -> Vaucluse,
You are too predictable, hehehehe,
Otherwise, I tried to sign up, I never received the confirmation email and in the end...
You are too predictable, hehehehe,
Otherwise, I tried to sign up, I never received the confirmation email and in the end...
Hello Appolonie.
The formula from Migou76 is correct. Indeed, the AVERAGE function takes into account the empty cells, which makes your concerns superfluous "as long as the month is not over, I can't do anything with these stats because I divide by 23 (working days) and not by the number of days actually entered, so all my averages are skewed".
However, you can choose either minutes or hundredths of an hour; it's the same.
In summary, the answer to your question is "YES, you just need to use the AVERAGE function."
Best regards.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
The formula from Migou76 is correct. Indeed, the AVERAGE function takes into account the empty cells, which makes your concerns superfluous "as long as the month is not over, I can't do anything with these stats because I divide by 23 (working days) and not by the number of days actually entered, so all my averages are skewed".
However, you can choose either minutes or hundredths of an hour; it's the same.
In summary, the answer to your question is "YES, you just need to use the AVERAGE function."
Best regards.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
Hello,
Before addressing the formatting issue, we need to resolve the input problem.
In Excel, 0.30 is a number that will be interpreted as 0.3 days in date or time formats because Excel's unit is 1 day. Thus, 0.3 days = 7.2 hours or 7 hours and 12 minutes.
For Excel to recognize hours, you must use the separator : (colon). 7:12 or 7:12:00 will be recognized.
For Excel to recognize dates, you need to use the separator / (slash).
Before addressing the formatting issue, we need to resolve the input problem.
In Excel, 0.30 is a number that will be interpreted as 0.3 days in date or time formats because Excel's unit is 1 day. Thus, 0.3 days = 7.2 hours or 7 hours and 12 minutes.
For Excel to recognize hours, you must use the separator : (colon). 7:12 or 7:12:00 will be recognized.
For Excel to recognize dates, you need to use the separator / (slash).