Need help with average formula

[genestoy]

I need some help formatting an "average" formula for excel 2010 please

I have 3 rows (B8 to M8, B9 to M9 and B10 to M10) for the months of the year that I need to get an average for. There are 36 cells that may or may not have dollar amounts in them and I need to get an average for all those cells that have dollar amounts in them. So if 25 cells have figures in them I need the average for those 25 cells not all 36. Cells may have 20 of them with dollar amounts or 32 etc. so the average needs to be calculated without the empty cells. How do it accomplish this?
Thanks in advance for any help.
Gene

[JeteMc]

According to its help page, "If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included", so you could use:

=AVERAGE(B8:M10)

Let us know if you have any questions.

[genestoy]

Sorry that does not work for me

[JeteMc]

Her is my interpretation of the spreadsheet you are describing.
The Average function is populating P7 and a table of sums and counts in N8:O10 feeds the check in P9.
You may also want to upload a sample of your spreadsheet. To attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[genestoy]

Maybe I explained it wrong - I have a total of 36 cells B8 to M8, B9 to M9 and B10 to M10 (some cells will be blank) I need the average of all cells that have dollar amounts and then divided by the number of months at present. So the total of all cells dollar amounts divided by 10 for the end of October, 11 for November etc.

[JeteMc]

The attached modification to the earlier spreadsheet uses the following formula in P10 to average the range up through the month displayed in P9:

=AVERAGE(INDIRECT("B8:"&ADDRESS(10,MATCH(P9,B7:M7,0)+1)))

Let us know if you have any questions.

[genestoy]

That did not work either but I found a "workaround"

I entered these 3 formulas in blank cells in the "O" column to get the average of each row

=IFERROR(AVERAGE(B4:M4),"")
=IFERROR(AVERAGE(B5:M5),"")
=IFERROR(AVERAGE(B6:M6),"")

Then I entered the code below in the "N" cell I needed the average total for the all the months entered so far

=SUM(O4,O5,O6)

The "N" cell now shows the correct average for all months entered so far and changes as more figures are entered in remaining months

Thanks for trying anyway it is appreciated, I just though there may be a single formula for the "N" cell to accomplish this.

Gene

[JeteMc]

Glad that you got a solution that works. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[JohnTopley]

I am not sure if this is correct mathematically as we still don't what is the three rows i.e are they the same "criterion" ?: but SUMming the averages is not the same as averaging all 36 assuming all 12 months were completed (or portion of).

[genestoy]

I have 3 cells for each month with dollar amounts in each cell (some may be blank)
All months entered need to be averaged, not just 1 of the cells

10.00   20.00  30.00  40.00 etc
15.00   25.00  35.00  45.00 etc
        10.00          5.00 etc (many are blank on the third row)

25.00   55.00  65.00  90.00 totals

                     58.75 average (235.00 divided by 4 months)

[JohnTopley]

You cannot have an average > than the maximum individual reading (45 in the data above): the average of those reading over 4 months is 235/10 (there are 10 readings).

JeteMc formula gave you the correct answer.

[Ihsienak]

Try This
=(AVERAGE(B4:M4,B6:M6,B8:M8))/(MONTH(DATEVALUE(C2&" 1")))

AVERAGE(B4:M4,B6:M6,B8:M8) averages the lines you wanted
MONTH(DATEVALUE(C2&" 1")) converts the months name to the months number
I Put the month into cell C2
You could use a hard number for the month or just use a number depending on hour your spreadsheet is set-up
The overall formula should achieve what you were looking for

I'm a rookie with excel but this should work

[genestoy]

Don't know what you mean here "I Put the month into cell C2"? Is this only going to do 1 month or all months?
thanks

[Ihsienak]

When i did my example testing i put " October" into Cell C2
The formula converted that to 10 and then divided the average of the cells by 10
If i put April into Cell C2 it divided the average by 4

[genestoy]

Sorry but

=(AVERAGE(B7:M7,B8:M8,B9:M9))/(MONTH(DATEVALUE(B2&" 1")))

returns about half of the actual average (changed the formula to my spreadsheet rows) If I change the B2 (Jan) to C2 (Feb) the result gets smaller and D2 (Mar) gets even smaller results
Gene

[Ihsienak]

ok
i misunderstood the question
I thought you wanted to divide the average by the month
if you use just the first part it will give the average
=AVERAGE(B4:M4,B6:M6,B8:M8)

Mike

[genestoy]

Still returns only half of what it should be, guess there is no way to do this but my way of the extra cells to calculate it does work so will stick with that. Thanks for trying everyone.

[JohnTopley]

my way of the extra cells to calculate it does work

sorry but it is mathematically incorrect.

What is in column A of these 3 rows?

[genestoy]

Name of sheet & year (in row 1)
Months Jan to Dec (in row 2)
Payee A (in row 3)
Payee A (in row 4)
Payee A (in row 5) this row usually has nothing in any of the cells but can in some months
blank row
Payee B (in row 7)
Payee B (in row 8)
Payee B (in row 9) this row usually has nothing in any of the cells but can in some months

[genestoy]

John, you are correct when the sheet has the three rows, it does not work. I have now deleted the third row and now it does work just fine. I will continue with the two rows--
Name of sheet & year (in row 1)
Months Jan to Dec (in row 2)
Payee A (in row 3)
Payee A (in row 4)
blank row
Payee B (in row 6)
Payee B (in row 7)