+ Reply to Thread
Results 1 to 7 of 7

Long formula --> #value | Zero values

  1. #1
    Registered User
    Join Date
    07-06-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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. #2
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    294

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

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

    Out of the Hat

    "Computers are stupid - they do EXACTLY what you tell them to"

    If I've helped you with a problem, please say thanks by clicking the small star icon on the left.

  3. #3
    Registered User
    Join Date
    07-06-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

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

    Fix the error values
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Registered User
    Join Date
    07-06-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    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. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

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

    =IFERROR(formula,"")

    Fixed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 03-15-2015, 03:12 PM
  2. [SOLVED] Transpose a long column into delimiters separated values according to values in input box
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-27-2014, 01:32 PM
  3. [SOLVED] =IF and =SUMIF formulas creating long long long data processing times.
    By comp in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-26-2014, 02:59 PM
  4. descriminate string(letters) values from integer/long (number) values
    By ag273n in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-21-2013, 03:12 AM
  5. Long long formula not calc'ing all steps
    By jvautour in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2009, 10:26 AM
  6. How long do vars keep their values in VBA?
    By MCCCLXXXV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2009, 11:00 AM
  7. long IF formula for multiple values.
    By davidbrown13 in forum Excel General
    Replies: 2
    Last Post: 09-03-2008, 12:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1