Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Is there a way to shorten this formula?

  1. #1
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is somewhat the same problem I had with a zero value in the divide cell. I am adding up every other cell, then dividing it by a total of several other non consecutive cells. This is my formula so far:
    =SUM(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49)

    I want to do something like what you showed me before with the =IF(A1=0,0,(rest of formula.

    Do I have to do the =IF and then list every one of these cells or is there a shorter way?

    [ This Message was edited by: Phylis Sophical on 2002-04-16 23:14 ]

  2. #2
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Once again, I think I answered my own question but I would like to know if others think this will work. I won't really know until I have some figures to put into the cells. Here's what I did:
    =IF(B4:B49=0,0,(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49))
    So the first part says to ignore any cells between B4 and B49 if they have a zero value. Otherwise, add the first bunch and divide by the second bunch. Does it look right?

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 23:12, Phylis Sophical wrote:
    Once again, I think I answered my own question but I would like to know if others think this will work. I won't really know until I have some figures to put into the cells. Here's what I did:
    =IF(B4:B49=0,0,(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49))
    So the first part says to ignore any cells between B4 and B49 if they have a zero value. Otherwise, add the first bunch and divide by the second bunch. Does it look right?
    Try
    =IF(SUM(B9,B17,B25,B33,B41,B49)=0,0,(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49))


    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 21:49, Phylis Sophical wrote:
    This is somewhat the same problem I had with a zero value in the divide cell. I am adding up every other cell, then dividing it by a total of several other non consecutive cells. This is my formula so far:
    =SUM(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49)

    I want to do something like what you showed me before with the =IF(A1=0,0,(rest of formula.

    Do I have to do the =IF and then list every one of these cells or is there a shorter way?

    [ This Message was edited by: Phylis Sophical on 2002-04-16 23:14 ]
    Hi Phylis,

    I'm entirely sure what you're trying to achieve here,
    I am adding up every other cell?

    =SUM(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49)

    isn't every other cell?

    then dividing it by a total of several other non consecutive cells.

    =SUM(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49)

    What is the relevance of these cells, what makes these cells the ones you need to use?

    Would you care to explain a little further. So I can better understand your problem.

    Many thanks

    "Have a good time......all the time"
    Ian Mac

  5. #5
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ian please excuse me for not making this more clear. Yes it was more than every other cell. I should know better. Part of the problem I find with Excel is trying to explain oneself as Excel is a language unto it's own. Here is a more detailed explanation:

    This is a questionaire that has 6 questions and a possibility of 5 diffrent responses from Outstanding to Poor. B4,12,20,28,36,44 represent Outstanding for each of the 6 questions.

    The other series, B9,17,25,33,41,49 represent the total amount of responses to each question. (All the Oustandings, Excellents, Goods, Okay's and Poor's combined)

    I am trying to determine what percentage of the total responses are Outstanding. And so on with the others. So I take all the outstandings and devide that by the total number of responses to all 6 questions.

    The formula I had, worked out fine except that if there is no value in the divisor cell, I get #DIV/0! in every cell I make this formula in. (1 formula cell for every Outstanding, Excellent, etc. times 12 months. That's 72 cells that I need this formula for a Summary of all questions. That's alot of #DIV/0!'s!!!!

    I noticed that people do post their worksheets but this takes up an intire 8 1/2 X 14" sheet.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 00:06, Phylis Sophical wrote:
    Ian please excuse me for not making this more clear. Yes it was more than every other cell. I should know better. Part of the problem I find with Excel is trying to explain oneself as Excel is a language unto it's own. Here is a more detailed explanation:

    This is a questionaire that has 6 questions and a possibility of 5 diffrent responses from Outstanding to Poor. B4,12,20,28,36,44 represent Outstanding for each of the 6 questions.

    The other series, B9,17,25,33,41,49 represent the total amount of responses to each question. (All the Oustandings, Excellents, Goods, Okay's and Poor's combined)

    I am trying to determine what percentage of the total responses are Outstanding. And so on with the others. So I take all the outstandings and devide that by the total number of responses to all 6 questions.

    The formula I had, worked out fine except that if there is no value in the divisor cell, I get #DIV/0! in every cell I make this formula in. (1 formula cell for every Outstanding, Excellent, etc. times 12 months. That's 72 cells that I need this formula for a Summary of all questions. That's alot of #DIV/0!'s!!!!

    I noticed that people do post their worksheets but this takes up an intire 8 1/2 X 14" sheet.
    OK then,

    What are the values in the 'outstanding' cells, the 'Excellent' etc.?

    I feel this may be better achieved using count. Putting the value into just 1 cell i.e. 1 to 5 = poor-outstanding.

    then working with that data.

    if you like you could e-mail an example of your problem and I could look further into it, there must a ton ways to achieve the desired result and would like to see which I could suggest.

    "Have a good time......all the time"
    Ian Mac

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    On the assumption that the two sets of numbers yield a useful Average.

    =IF(SUM(B9,B17,B25,B33,B41,B49),(SUM(B4,B12,B20,B28,B36,B44)/SUM(B9,B17,B25,B33,B41,B49)),"")

    or name the two sets of numbers

    =IF(SUM(rY),SUM(rX)/SUM(rY))

Some videos you may like

User Tag List

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
  •