Change automatically all formulas only from specific rows by adding identical content to them

V

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
359
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello!

I have a larger table arranged in a structure like the attached one. All cells have formulas, but those from specific rows (e.g. C23:K23, C27:K27, C28:K28 etc. ) contain formulas type "=C21/SUM(C21:K21), D21/SUM(D21:L21)" etc. and "=C25/AT25, D25/AT25" etc., that generate #DIV/0! error. In these circumstances, I want to change their formulas in some like from E3 - IFERROR(C21/SUM(C21:K21),0), to avoid the error. Is any way I could do it only in specific rows #DIV/0! errors and change the structure of all formulas at once, by adding the additional content (=IFERROR(.........................),0) ?

Thank you in advance!
Buget de venituri si cheltuieli 2025.xlsx
CDEFGHIJK
210.000.000.000.000.000.000.000.000.00
220.00
230.00%#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
240.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
250.000.000.000.000.000.000.000.000.00
260.00
27#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
28#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
290.000.000.000.000.000.000.000.000.00
300.00
31#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
32#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
Grafic evolutie buget
Cell Formulas
RangeFormula
C21:K21C21=C3
C22,C26,C30C22=SUM(C21:K21)
C23C23=IFERROR(C21/SUM(C21:K21),0)
D23,C27:D27,C31:D31D23=D21/SUM(D21:L21)
E23,E27,E31E23=E21/SUM(C21:K21)
F23,F27,F31F23=F21/SUM(C21:K21)
G23,G27,G31G23=G21/SUM(C21:K21)
H23,H27,H31H23=H21/SUM(C21:K21)
I23,I27,I31I23=I21/SUM(C21:K21)
J23,J27,J31J23=J21/SUM(C21:K21)
K23,K27,K31K23=K21/SUM(C21:K21)
C24,C28,C32C24=C21/AT21
D24,D28,D32D24=D21/AT21
E24,E28,E32E24=E21/AT21
F24,F28,F32F24=F21/AT21
G24,G28,G32G24=G21/AT21
H24,H28,H32H24=H21/AT21
I24,I28,I32I24=I21/AT21
J24,J28,J32J24=J21/AT21
K24,K28,K32K24=K21/AT21
C25:K25C25=C4
C29:K29C29=C5
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
vladimiratanasiu said:
Hello!

I have a larger table arranged in a structure like the attached one. All cells have formulas, but those from specific rows (e.g. C23:K23, C27:K27, C28:K28 etc. ) contain formulas type "=C21/SUM(C21:K21), D21/SUM(D21:L21)" etc. and "=C25/AT25, D25/AT25" etc., that generate #DIV/0! error. In these circumstances, I want to change their formulas in some like from E3 - IFERROR(C21/SUM(C21:K21),0), to avoid the error. Is any way I could do it only in specific rows #DIV/0! errors and change the structure of all formulas at once, by adding the additional content (=IFERROR(.........................),0) ?

Thank you in advance!
Buget de venituri si cheltuieli 2025.xlsx
CDEFGHIJK
210.000.000.000.000.000.000.000.000.00
220.00
230.00%#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
240.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
250.000.000.000.000.000.000.000.000.00
260.00
27#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
28#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
290.000.000.000.000.000.000.000.000.00
300.00
31#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
32#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
Grafic evolutie buget
Cell Formulas
RangeFormula
C21:K21C21=C3
C22,C26,C30C22=SUM(C21:K21)
C23C23=IFERROR(C21/SUM(C21:K21),0)
D23,C27:D27,C31:D31D23=D21/SUM(D21:L21)
E23,E27,E31E23=E21/SUM(C21:K21)
F23,F27,F31F23=F21/SUM(C21:K21)
G23,G27,G31G23=G21/SUM(C21:K21)
H23,H27,H31H23=H21/SUM(C21:K21)
I23,I27,I31I23=I21/SUM(C21:K21)
J23,J27,J31J23=J21/SUM(C21:K21)
K23,K27,K31K23=K21/SUM(C21:K21)
C24,C28,C32C24=C21/AT21
D24,D28,D32D24=D21/AT21
E24,E28,E32E24=E21/AT21
F24,F28,F32F24=F21/AT21
G24,G28,G32G24=G21/AT21
H24,H28,H32H24=H21/AT21
I24,I28,I32I24=I21/AT21
J24,J28,J32J24=J21/AT21
K24,K28,K32K24=K21/AT21
C25:K25C25=C4
C29:K29C29=C5
Click to expand...
I missed to mention, but I need the same changes ("=IFERROR(C25/AT25),0)") also for the formulas type =C25/AT25, D25/AT25 etc.
 
Last edited:
Upvote 0
Hi,
I would change one cell and copy/paste only the formula for each cell that match the same formula. Also, you should minimise repetition, sum is already done in row 22,26 and 30.

eg:
in C23, i would write:
Excel Formula: 
=IFERROR(C21/$C22,0)
then i would copy then paste only the formula to all the green cells.
 
Upvote 0
coulombevin said:
Hi,
I would change one cell and copy/paste only the formula for each cell that match the same formula. Also, you should minimise repetition, sum is already done in row 22,26 and 30.

eg:
in C23, i would write:
Excel Formula: 
=IFERROR(C21/$C22,0)
then i would copy then paste only the formula to all the green cells.
Click to expand...
Thank you for the answer! Unfortunately my table is much more larger in columns and in rows, and it's very difficult to do this task manually. Also, some intermediate cells are merged or have different formulas from the ones mentioned in my initial message, and there is a risk to block / change their content if I use this method..
 
Last edited:
Upvote 0
Hi vladimiratanasiu,

In that case, if you don't know how to use VBA (developper mode), I would start by replacing (ctrl + h) equal symbol "=" by a symbol of your choice (eg, "[" or "_" ) that is not found in your formulas. This way you can substitute "SUM(" with "IFERROR(SUM(" and ")" with "),0)" without facing an error of missing parenthesis. After that, i would change the chosen symbol back to "=".
 
Upvote 0
coulombevin said:
Hi vladimiratanasiu,

In that case, if you don't know how to use VBA (developper mode), I would start by replacing (ctrl + h) equal symbol "=" by a symbol of your choice (eg, "[" or "_" ) that is not found in your formulas. This way you can substitute "SUM(" with "IFERROR(SUM(" and ")" with "),0)" without facing an error of missing parenthesis. After that, i would change the chosen symbol back to "=".
Click to expand...
If you know / can edit a specific macro proper to solve this problem, please post it and I will adjust it to my worksheet. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
119
earth
E
D
  • Question Question
Scoring weighted permutations
Replies
1
Views
243
StephenCrump
S
W
  • Question Question
Adding another condition to IF(AND) formula
Replies
6
Views
286
etaf
etaf
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
976
paulkc
P

Forum statistics

Threads
1,225,635
Messages
6,186,129
Members
453,340
Latest member
Stu61

Share this page

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