Average works, subtotal on same range gives #DIV/0! error

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
435
Office Version
  1. 365
  2. 2010
Hello

Does anybody have any idea why
Excel Formula:
=Subtotal(101, range)
would give #DIV/0! error, when
Excel Formula:
=Average(range)
works (i.e. returns a number) on exactly the same range?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
That would only happen if all numbers in the range were in hidden rows.
 
Upvote 0
Hmm - that's not the case in my case.

In fact
Excel Formula:
=Subtotal(1, range)
also gives the #DIV/0! error...with or without any hidden rows.
 
Upvote 0
...and
Excel Formula:
=AGGREGATE(1,4,range)
and
Excel Formula:
=AGGREGATE(1,5,range)
also both work,

Just both subtotals 1, and 101 produce that error - weirdly

All the cells in the range are formatted as numbers. The values are numerical or "". They're in a table and the range is referenced tablename[column name].

I can't work out why subtotal doesn't work.
 
Upvote 0
Any circular references? (formulas in 'range' that refer back to the subtotal formula).

Without seeing the data and any formulas involved, I think it will be near impossible to diagnose the cause. I'm unable to produce an error in subtotal without also getting one in average.
 
Upvote 0
I don't think there are any circular references.
Excel Formula:
=AGGREGATE(1,5,tbl_Events[Days since last Referral for this patient])
Excel Formula:
=AVERAGE(tbl_Events[Days since last Referral for this patient])
Excel Formula:
=SUBTOTAL(1,tbl_Events[Days since last Referral for this patient])

The first two both produce an average (10.34), the last one #DIV/0!. The formula in each cell in the column tbl_Events[Days since last Referral for this patient] (> 400 rows) is:

Excel Formula:
=IF([@Event]="Referral","",IFERROR([@[Event Date]]-1/(1/AGGREGATE(14,6,[Event Date]/([Event]="Referral")/([Patient Number]=[@[Patient Number]])/([Event Date]<=[@[Event Date]]),1)),""))

I've filled it down to all the cells in the table, but I've also noticed that if I add a Total Row to the Table, and select average it also returns a #DIV/0! error.

Is there some way to determine which cells it finds difficult?
 
Upvote 0
Add another column to tbl_Events with the formula,
Excel Formula:
=SUBTOTAL(1,[@Days since last Referral for this patient])
Do you see errors on every row, or just some of them?
 
Upvote 0
Sorry if I wasn't clear. That formula

Excel Formula:
=SUBTOTAL(1,[@Days since last Referral for this patient])

is just in once cell referencing the whole column. Each cell in the column has this formula in it

Excel Formula:
=IF([@Event]="Referral","",IFERROR([@[Event Date]]-1/(1/AGGREGATE(14,6,[Event Date]/([Event]="Referral")/([Patient Number]=[@[Patient Number]])/([Event Date]<=[@[Event Date]]),1)),""))

There aren't any errors showing in the cells
 
Upvote 0
No no no, if you look at the SUBTOTAL formula, you will see that I removed the table name and added an @ symbol in order to validate each row of the table individually.

Even if there are no errors at present, this might help to identify the cause of the errors in the subtotal formula.
 
Upvote 0
No no no, if you look at the SUBTOTAL formula, you will see that I removed the table name and added an @ symbol in order to validate each row of the table individually.

Even if there are no errors at present, this might help to identify the cause of the errors in the subtotal formula.
Sorry I misunderstood.
Were you suggesting
Excel Formula:
=SUBTOTAL(1,[@[Days since last Referral for this patient]])
in a new column in each row? That produces a #DIV/0! error in every row,


Excel Formula:
=AGGREGATE(1,4,[@[Days since last Referral for this patient]])
Excel Formula:
=AVERAGE([@[Days since last Referral for this patient]])

produce #DIV/0 only where there's a blank cell
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top