Editing subtotal formulas?

Case

New Member
Joined
Nov 2, 2004
Messages
26
Can anyone explain how I can add a rounding formula to the formulas created in the subtotal fetaure? i.e. when I apply subtotal, the formula created in the subtotal line is =subtotal(9,e1:e100) - but I need to have that rounded to the nearest 100 so I would like it to read =round(subtotal(9,e1:e100)-2). I have numerous rows and don't want to edit each individually

My other option is to combine the two formulas from different cells in a third cell, similar to the concatenate function but with formulas. Is this possible too?

Thanks
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
Try:

=ROUND(SUBTOTAL(9,E1:E100),-1)
 

Case

New Member
Joined
Nov 2, 2004
Messages
26
That's the formula I want but how can I edit the forumlas created in subtotal, without doing each one separately?
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
Can't you just copy and pastespecial the new formula to the other cells which require it, or use the fill handle?
 

Case

New Member
Joined
Nov 2, 2004
Messages
26
Because there are different amounts of rows within each subtotal, I can't just copy the new formula. When I copy or use the fill handle, it uses the subtotal range in each of the following subtotals, which then means those are adding up incorrectly
 

Watch MrExcel Video

Forum statistics

Threads
1,118,016
Messages
5,569,628
Members
412,284
Latest member
Daibear
Top