Excel formula help please?

B

blue-eye-boy

Expert Member
Joined
Jul 25, 2006
Messages
2,973
Reaction score
5
I use openoffice, but the formula would be the same in any excel. So what I need, I'm creating a spreadsheet, on the left I have numbered from 1 to 31 (dates), and top is the months, from left to right.

We have to keep track of a certain stock level during each month. So we will every day weigh this stock, and fill in the level on each date. The at the end of the month I want to get an average for the whole month. But, if every month had the same amount of working days, it would be easy, but each month does not have. So then I could have set the formula for example (sum(a1:a31))/25. But how do I set a formula to automatically adjust to how many working days there were in that month?

Thanks in advance
 
Simple. Leave 31 rows open for days and put your total in row 32. Now if there are only 28 days then you'll have a few empty rows before the total but that doesn't matter.

To get the average just use:

Code: 
=AVERAGE(A1:A31)
 
Yes I can do that, but if there's a problem with the stock level, I want to be able to pin point it to a exact day.
 
Use COUNTA

Don't have Excel here with me now so cannot look it up but what it does is count non empty cells. So what you do is count an area of say 35 cells (5 rows of 7 days each - just an example). If you only filled in values in say 25 cells then the result from the COUNTA will be 25. With a month where you complete say 30 days the result will be 30.

So your formula will look like this: =Sum(A10:K50)/COUNTA(A10:K50,"")

Just check the COUNTA formula, not sure I have it right in above example.
 
MKFrost said:
Use COUNTA

Don't have Excel here with me now so cannot look it up but what it does is count non empty cells. So what you do is count an area of say 35 cells (5 rows of 7 days each - just an example). If you only filled in values in say 25 cells then the result from the COUNTA will be 25. With a month where you complete say 30 days the result will be 30.

So your formula will look like this: =Sum(A10:K50)/COUNTA(A10:K50,"")

Just check the COUNTA formula, not sure I have it right in above example.
Click to expand...

If I understand what you are saying correctly then this is redundant. Using a formula like AVERAGE or SUM will already only count cells with numeric values in them. If they are blank it will not count towards the total for the average.
 
So then use conditional formatting to highlight cells that are above a certain value. Easiest way to pin point anomalies.
 
And btw a COUNTIF formula basically treats a blank cell as not being greater than 0
 
Maybe you looking for the NETWORKDAYS function which counts the number of working days between 2 dates. You just have to watch out for public holidays.
 
I still don't understand why the first solution would be an issue. According to the way he says the sheet is set up this should be no problem. The end of the sheet would look like this then if I understand correctly:

Code: 
Day   Jan   Feb   ...
..      
..    
..    
28    453   254
29    234   
30    987   
31    123   
AVG   400   349
 
DrJohnZoidberg said:
I still don't understand why the first solution would be an issue. According to the way he says the sheet is set up this should be no problem. The end of the sheet would look like this then if I understand correctly:

Code: 
Day   Jan   Feb   ...
..      
..    
..    
28    453   254
29    234   
30    987   
31    123   
AVG   400   349
Click to expand...

Agreed. I don't think he himself knows what he wants.
 
deanstein said:
Agreed. I don't think he himself knows what he wants.
Click to expand...
Ag you know what, your post is really very helpful. Thanks

I will check on all the other helpful posts. And I know what I want. There will be a stock level filled in every WORKING day. So some months have more working days than others, so the average cant be calculated the same each month. The sum of all stock levels yes, but not the amount of days, on which the average must be calculated.

If I can explain it like this, the sum isn't the issue, that's easy, but the formula must be "able" to "see" how many cells were filled that month, and work out the average for the month.
 
jacqvt said:
Ag you know what, your post is really very helpful. Thanks

I will check on all the other helpful posts. And I know what I want. There will be a stock level filled in every WORKING day. So some months have more working days than others, so the average cant be calculated the same each month. The sum of all stock levels yes, but not the amount of days, on which the average must be calculated.

If I can explain it like this, the sum isn't the issue, that's easy, but the formula must be "able" to "see" how many cells were filled that month, and work out the average for the month.
Click to expand...

I think you've missed my previous posts then. The AVERAGE function will not take in to consideration empty cells even if you select 500 cells, it will only calculate the average based on the cells that contain values. So if there are 31 cells and only 28 contain values it will only work out the average for 28 cells.
 
DrJohnZoidberg said:
I think you've missed my previous posts then. The AVERAGE function will not take in to consideration empty cells even if you select 500 cells, it will only calculate the average based on the cells that contain values. So if there are 31 cells and only 28 contain values it will only work out the average for 28 cells.
Click to expand...

But make sure that the cells are empty and not filled with a 0 unless there is in fact 0 in stock at the time
 
DrJohnZoidberg said:
I think you've missed my previous posts then. The AVERAGE function will not take in to consideration empty cells even if you select 500 cells, it will only calculate the average based on the cells that contain values. So if there are 31 cells and only 28 contain values it will only work out the average for 28 cells.
Click to expand...
Thanks for that post, I did see it. I just explained myself again for those who didn't understand me the first time. Thanks again for al the replies
 
Just on a side note openoffice pales into comparison with MS Office. BUT there's always Kingsoft/WPS office which is great. This coming from a linux & oss supporter.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X