Long formula --> #value | Zero values

1. Long formula --> #value | Zero values

Hi

I have this formula =\$EU\$80*ET80+\$EU\$79*ET79+\$EU\$78*ET78+\$EU\$77*ET77+\$EU\$76*ET76+\$EU\$75*ET75+\$EU\$74*ET74+\$EU\$73*ET73+\$EU\$72*ET72+\$EU\$71*ET71+\$EU\$70*ET70+\$EU\$69*ET69+\$EU\$68*ET68+\$EU\$67*ET67+\$EU\$66*ET66+\$EU\$65*ET65+\$EU\$64*ET64+\$EU\$63*ET63+\$EU\$62*ET62+\$EU\$61*ET61+\$EU\$60*ET60+\$EU\$59*ET59+\$EU\$58*ET58+\$EU\$57*ET57+\$EU\$56*ET56+\$EU\$55*ET55+\$EU\$54*ET54+\$EU\$53*ET53+\$EU\$52*ET52+\$EU\$51*ET51+\$EU\$50*ET50+\$EU\$49*ET49+\$EU\$48*ET48+\$EU\$47*ET47+\$EU\$46*ET46+\$EU\$45*ET45+\$EU\$44*ET44+\$EU\$43*ET43+\$EU\$42*ET42+\$EU\$41*ET41+\$EU\$40*ET40+\$EU\$39*ET39+\$EU\$38*ET38+\$EU\$37*ET37+\$EU\$36*ET36+\$EU\$35*ET35+\$EU\$34*ET34+\$EU\$33*ET33+\$EU\$32*ET32+\$EU\$31*ET31+\$EU\$30*ET30+\$EU\$29*ET29+\$EU\$28*ET28+\$EU\$27*ET27+\$EU\$26*ET26+\$EU\$25*ET25+\$EU\$24*ET24+\$EU\$23*ET23+\$EU\$22*ET22+\$EU\$21*ET21+\$EU\$20*ET20+\$EU\$19*ET19+\$EU\$18*ET18+\$EU\$17*ET17+\$EU\$16*ET16+\$EU\$15*ET15+\$EU\$14*ET14+\$EU\$13*ET13+\$EU\$12*ET12+\$EU\$11*ET11+\$EU\$10*ET10+\$EU\$9*ET9+\$EU\$8*ET8+\$EU\$7*ET7+\$EU\$6*ET6

where EU is a column with weights and ET is a column with data, how do I edit the formula to give actual values instead of #value, if one of data cells has a zero or error value ?

Currently the formula only gives values if there is data in all the cells

2. Re: Long formula --> #value | Zero values

Use the SUMPRODUCT function:
=SUMPRODUCT(\$EU\$6:\$EU\$80,\$ET\$6:\$ET\$80)
It treats errors as zeroes.

3. Re: Long formula --> #value | Zero values

the data is for many more cells and that formula didnt work, is there an amendment or a different one i could use ?

4. Re: Long formula --> #value | Zero values

Fix the error values

5. Re: Long formula --> #value | Zero values

error values can't be fixed as it uses real world surveys and for some years there is no data, is there another option ?

6. Re: Long formula --> #value | Zero values

Try this ...

=SUM(IF(ISNUMBER(\$EU\$6:\$EU\$80*\$ET\$6:\$ET\$80),\$EU\$6:\$EU\$80*\$ET\$6:\$ET\$80,""))

Enter with Ctrl+Shift+Enter.

7. Re: Long formula --> #value | Zero values

=IFERROR(formula,"")

Fixed.

There are currently 1 users browsing this thread. (0 members and 1 guests)