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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,884
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,884
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,529
Members
431,883
Latest member
Hien

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
Top