Adding and deleting rows with formulas ****Need Help****

[jperkin3]

I have built a evaluation scorecard and want to be able to allow others
to use it without always having to do it for them as they know nothing
about formulas. I have multiple rows that have a seperate weight factor
for each and a score of 1-5 for each. These roll down to a total box
that does all the calculations. If I delete of add a row I get the
#REF in my totals.Is there anything I can do to make the formula update
with the addition or subtraction of the rows? Thank you in advance.

 

[Daniel CHEN]

Try to use Offset function to create a dynamic range in your formula, so
when you delete/insert rows, the range automatically changes.
Example (try this by yourself):

In Cell B11 type the following formula, which sums all data from B1 till
B10 -

=SUM(OFFSET(B11,1-ROW(B11),0,ROW(B11)-1,1))

You can delete/inserts rows between B1 and B10.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================

 

[R. Choate]

This sounds to me like you have a formula that looks something like =(+f3+f4+f5+etc.,etc.,etc.). If you delete any of the cells
listed then you get the error you mentioned. If that is the case for you then change your formula to something like (f3:f6). This
format will not throw an error if you delete a row.

HTH
--
RMC,CPA


I have built a evaluation scorecard and want to be able to allow others
to use it without always having to do it for them as they know nothing
about formulas. I have multiple rows that have a seperate weight factor
for each and a score of 1-5 for each. These roll down to a total box
that does all the calculations. If I delete of add a row I get the
#REF in my totals.Is there anything I can do to make the formula update
with the addition or subtraction of the rows? Thank you in advance.

 

[R. Choate]

Why not just use =sum(B1:B11) and keep the formula in B12 while hiding row 11, assuming there is a need to allow users to insert and
add data below row 10 and have it included? The formula you suggest seems like overkill for a simple sum. Perhaps I'm missing
something.
--
RMC,CPA


Try to use Offset function to create a dynamic range in your formula, so
when you delete/insert rows, the range automatically changes.
Example (try this by yourself):

In Cell B11 type the following formula, which sums all data from B1 till
B10 -

=SUM(OFFSET(B11,1-ROW(B11),0,ROW(B11)-1,1))

You can delete/inserts rows between B1 and B10.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================

 

[bperks]

Daniel,

The formula I use to do this is :
=SUM(F13*E13+F14*E14+F15*E15+F16*E16+F17*E17+F18*E18)/5*E12

Can the OFFSET function work when it involves seperate colums?

Thanks

 

[R. Choate]

Have you tried using the sumproduct function?

By the way, without offense intended to anybody, you would be wise to get the most help you can from the Microsoft MVPs that help
people on this site all the time. I've read Daniel's site and his FAQ and I promise you that sites like Ozgrid.com and
Contextures.com and the many other MVP-based sites will give you the best answers to questions like this. Array formulas are often
avoided by better alternatives which do not require ctrl+shift+enter and are generally simpler and easier to use. Trust me on this.

HTH
--
RMC,CPA


Daniel,

The formula I use to do this is :
=SUM(F13*E13+F14*E14+F15*E15+F16*E16+F17*E17+F18*E18)/5*E12

Can the OFFSET function work when it involves seperate colums?

Thanks