How to use COUNT functions to subtract 1

I am trying to take the SUM of the number is the first row and divide by the COUNT of the cells that have numbers MINUS one of that COUNT.

Any ideas?

I can't attach a screen shot, but the data is monthly values. I want to compare the value of JUN (in this case 27) to the average of the preceding 5 months. I do not want to change manually the number of months in excel to divide by 5 as the COUNT functions bring 6. Attempts at formula simply subtract 1 from the average of the 6 months.

I attached an extremely rudimentary version to show the intent. Might help?

? How can this be set up so when a user inputs or data is pulled for follow-on months the number is auto populate

How's this?

Formula for O3

=SUM(INDIRECT(ADDRESS(3,2)&":"&ADDRESS(3,2+COUNTIF(B3:M3,"<>"&"")-2)))

Formula for P3

=INDIRECT(ADDRESS(3,2+COUNTIF(B3:M3,"<>"&"")-1))-SUM(INDIRECT(ADDRESS(3,2)&":"&ADDRESS(3,2+COUNTIF(B3:M3,"<>"&"")-2)))/(COUNTIF(B3:M3,"<>"&"")-1)

I probably didn't do the best job at the explaining. In the example, the yellow box would need to be 6 (the total of 30 divided by the previous 5 months) and the blue box would be 6.

Putting in 20 for JUL should give the result of 7 (42 divided by 6 months) and the blue box would be 13. So the Blue looks good testing it out.

P3

=indirect(address(3,2+countif(b3:m3,"<>"&"")-1))-sum(indirect(address(3,2)&":"&address(3,2+countif(b3:m3,"<>"&"")-2)))/(countif(b3:m3,"<>"&"")-1)

o3

=sum(indirect(address(3,2)&":"&address(3,2+countif(b3:m3,"<>"&"")-2)))/(countif(b3:m3,"<>"&"")-1)

perfect! I was not familiar with this logic at all and great to learn! Will be useful

Or just:
In O3:
=AVERAGE(OFFSET($B$3:$M$3,0,0,1,COUNT($B$3:$M$3)-1))

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Response 8 worked. I just made one simple change to look for values greater than 0 as total will always be 0.

I now run into issue try to find value comparing the month % to the cumulative % for the months prior. I attached a file that explains. Would appreciate insight. What I am trying to do is quickly compare the current month value to the previous months averages.

I am curious to test out the COUNTA function as that is the route I was trying for a few hours last week.

No file attached.

Attach a sample workbook (not image).

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.

From your file (removing #DIV0! error then compare in Q4 would be July/ Jun.

However as you data in all months in row 5 there is no method of testing which is current month.

and this applies to Q5

in P2

=AVERAGE(OFFSET($B2,0,1,1,COUNTIF($C2:$N2,"<>0")-1))

in P3

=AVERAGE(OFFSET($B3,0,1,1,COUNTIF($C3:$N3,"<>0")-1))

Put this in C5 and copy across

=IF(C3=0,0,SUM($C3:C3)/SUM($C2:C2))

in C4 and copy across

=IFERROR(C3/C2,0)

in Q4

=OFFSET($B4,,COUNTIF($C4:$N4,"<>0"))-OFFSET($B$5,,COUNTIF($C5:$N5,"<>0")-1)

in Q5

=OFFSET($B5,,COUNTIF($C5:$N5,"<>0"))-OFFSET($B$5,,COUNTIF($C5:$N5,"<>0")-1)

#18 worked perfectly, much appreciated and learned quite a bit from example. Will keep this in my toolkit as method to determine current month data to previous months

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.