Is there a way to shorten this formula?

[Phylis Sophical]

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

 

[Phylis Sophical]

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?

 

[Ivan F Moala]

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))

 

[Ian Mac]

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

 

[Phylis Sophical]

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.

 

[Ian Mac]

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.

 

[Dave Patton]

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))