Formula help
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Formula help

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula help

    Hi, everybody.

    I have numbers like a/b, formatted as text, and I need to sum separately the numbers before the slash (a) and separately the numbers behind the slash (b) to get a result in the same a/b format. Example: 1/2 + 7/1 = 8/3. I have created a formula that works, but has problem with blank cells. If there is a blank cell in the specified range, the formula shows #VALUE !. The formula is:

    =CONCATENATE(SUMPRODUCT(VALUE(LEFT(B1:G1;FIND("/";B1:G1)-1)));"/";SUMPRODUCT(VALUE(RIGHT(B1:G1;LEN(B1:G1)-FIND("/";B1:G1)))))

    I tried to force it to ignore the blank cells by adding the --(B3:G3<>"") string like this:

    =CONCATENATE(SUMPRODUCT(VALUE(LEFT(B3:G3;FIND("/";B3:G3)-1));--(B3:G3<>""));"/";SUMPRODUCT(VALUE(RIGHT(B3:G3;LEN(B3:G3)-FIND("/";B3:G3)));--(B3:G3<>"")))

    but it shows #VALUE ! again.

    Interesting is, when I change --(B3:G3<>"") to --(B3:G3<>"1/1") or any other number 2/2, 5/4 etc., then the formula works perfectly and ignores all cells containing the specified number. But with "" it does not work. What do I wrong ? Could someone help me to solve this please ?

  2. #2
    Board Regular
    Join Date
    Oct 2015
    Location
    Suffolk, UK
    Posts
    205
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    Untested - Put a space between the speech marks?

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    The same result #VALUE !

  4. #4
    Board Regular
    Join Date
    Oct 2015
    Location
    Suffolk, UK
    Posts
    205
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    =CONCATENATE(SUMPRODUCT(VALUE(LEFT(B1:G1,FIND("/",B1:G1)-1))),"/",SUMPRODUCT(VALUE(RIGHT(B1:G1,LEN(B1:G1)-FIND("/",B1:G1)))))

    Recreated and inserted random spaces, your first formula works. I don't know why you are using semi colons though..... " ; "

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    Yes, the first formula works until there is no blank cell in range B1:G1. But if there is one, the formula shows #VALUE ! Therefore I added --(B1:G1<>"") strings to the formula awaiting it will ignore blank cells, but it does not. If there is a blank cell, it shows #VALUE ! again.

    =CONCATENATE(SUMPRODUCT(VALUE(LEFT(B1:G1;FIND("/";B1:G1)-1));--(B1:G1<>""));"/";SUMPRODUCT(VALUE(RIGHT(B1:G1;LEN(B1:G1)-FIND("/";B1:G1)));--(B1:G1<>"")))

    I must use semi colons, if I use commas instead, excel does not accept it. I have Excel 2013.

  6. #6
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    The problem is here:

    --(B1:G1<>"1/1") formula works, ignores all cells containing 1/1
    --(B1:G1<>"5/2") formula works, ignores all cells containing 5/2
    --(B1:G1<>"") formula does not work, does not ignores blank cells, shows #VALUE !, the same result if apostrophe is used instead of "
    --(B1:G1<>" ") formula does not work, does not ignores blank cells, shows #VALUE !, the same result if apostrophe is used instead of "

  7. #7
    Board Regular
    Join Date
    Oct 2015
    Location
    Suffolk, UK
    Posts
    205
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    Until someone comes up with a GENIUS solution a quick fix would be......

    Select the entire range that contains your data, select FIND/REPLACE from the ribbon leave the FIND Field empty and the REPLACE Field 0/0, click OK.

    This will ensure ALL areas of your data range contain a value an prevent the error from occurring, not a pretty solution.

  8. #8
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    Yes, that's my backup plane if I don't solve the problem with the formula. Thanks for the effort, Tony.

  9. #9
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,293
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    The following single cell array formula works for me:

    =CONCATENATE(SUMPRODUCT(IF(LEN(B1:G1)>0;VALUE(LEFT(B1:G1;FIND("/";B1:G1)-1));0));"/";SUMPRODUCT(IF(LEN(B1:G1)>0;VALUE(RIGHT(B1:G1;LEN(B1:G1)-FIND("/";B1:G1)));0)))
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,115
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Formula help

    Either control+shift+enter, not just enter:

    =SUM(LEFT(0&B1:G1,FIND("/",0&B1:G1&"/")-1)+0)&"/"&SUM(REPLACE(B1:G1,1,FIND("/",0&B1:G1&"/")-1,"0")+0)

    Or just enter:

    =SUMPRODUCT(LEFT(0&B1:G1,FIND("/",0&B1:G1&"/")-1)+0)&"/"&SUMPRODUCT(REPLACE(B1:G1,1,FIND("/",0&B1:G1&"/")-1,"0")+0)
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com