sumdivide array formula

[Doug Broad]

Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

A       B          C
1  Room  Area    Area/Person
2  Break 144.10  100
3  Office 157.70   50
4  Hall
5  Toilet
6  Stacks 785.00 100
.....
Total Occupants:  ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values.  Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C6>0)),B1:B6/C1:C6))}

but it did not work.  Sumproduct would work if I was trying
to multiply.  Anyone have any ideas?  Thanks.

 

[Don Guillett]

insert>name>define
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC>0)),rngB/rngC))}

 

[Doug Broad]

Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C6>0,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad

insert>name>define
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC>0)),rngB/rngC))}

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

A       B          C
1  Room  Area    Area/Person
2  Break 144.10  100
3  Office 157.70   50
4  Hall
5  Toilet
6  Stacks 785.00 100
....
Total Occupants:  ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values.  Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C6>0)),B1:B6/C1:C6))}

but it did not work.  Sumproduct would work if I was trying
to multiply.  Anyone have any ideas?  Thanks.
[/QUOTE]
[/QUOTE]

 

[Don Guillett]

"and" function, would not work. Perhaps that

is a limitation of the array formula.

The "and" function should work just fine. What I sent was a way to name your
ranges so they would be dynamic and then you could use them in the NON array
sumproduct function.

If you want to send me a SMALL workbook outlining your problem and giving
FULL DETAILS, I will take a look.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C6>0,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad

insert>name>define
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC>0)),rngB/rngC))}

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

A       B          C
1  Room  Area    Area/Person
2  Break 144.10  100
3  Office 157.70   50
4  Hall
5  Toilet
6  Stacks 785.00 100
....
Total Occupants:  ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values.  Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C6>0)),B1:B6/C1:C6))}

but it did not work.  Sumproduct would work if I was trying
to multiply.  Anyone have any ideas?  Thanks.
[/QUOTE]
[/QUOTE]
[/QUOTE]

 

[Doug Broad]

Thanks for the offer Don.
In this case, the workbook is 440K, with hundreds of macros
in it. I could cut and paste the relevant part of it but I described
it pretty well other than the exact location of the table.

It's part of a building code summary worksheet that is required
in North Carolina. The columns were right, but the rows are
169-??? depending on how many rooms there are in the project.
There is data above and below the table to be calculated. Perhaps
the counta function arguments should be refined?

In your illustration, would rngB need to be dynamically defined
as well as rngC? I tried defining it as a regular range and it didn't work.
It didn't occur to me that you were bypassing the need for an array
formula since you included the braces in your example. But with
my guess at how rngB was defined, it didn't work.

Thanks again.

Regards,
Doug

"and" function, would not work. Perhaps that
is a limitation of the array formula.

The "and" function should work just fine. What I sent was a way to name your
ranges so they would be dynamic and then you could use them in the NON array
sumproduct function.

If you want to send me a SMALL workbook outlining your problem and giving
FULL DETAILS, I will take a look.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C6>0,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad

insert>name>define
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC>0)),rngB/rngC))}

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

A       B          C
1  Room  Area    Area/Person
2  Break 144.10  100
3  Office 157.70   50
4  Hall
5  Toilet
6  Stacks 785.00 100
....
Total Occupants:  ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values.  Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C6>0)),B1:B6/C1:C6))}

but it did not work.  Sumproduct would work if I was trying
to multiply.  Anyone have any ideas?  Thanks.
[/QUOTE]
[/QUOTE]
[/QUOTE]

 

[Tom Ogilvy]

=SUM((ISNUMBER(C1:C6))*(C1:C6>0)*IF(ISNUMBER(B1:B6/C1:C6),B1:B6/C1:C6))

Array entered will work

--
Regards,
Tom Ogilvy

Thanks for the offer Don.
In this case, the workbook is 440K, with hundreds of macros
in it. I could cut and paste the relevant part of it but I described
it pretty well other than the exact location of the table.

It's part of a building code summary worksheet that is required
in North Carolina. The columns were right, but the rows are
169-??? depending on how many rooms there are in the project.
There is data above and below the table to be calculated. Perhaps
the counta function arguments should be refined?

In your illustration, would rngB need to be dynamically defined
as well as rngC? I tried defining it as a regular range and it didn't work.
It didn't occur to me that you were bypassing the need for an array
formula since you included the braces in your example. But with
my guess at how rngB was defined, it didn't work.

Thanks again.

Regards,
Doug

"and" function, would not work. Perhaps that
is a limitation of the array formula.

The "and" function should work just fine. What I sent was a way to name your
ranges so they would be dynamic and then you could use them in the NON array
sumproduct function.

If you want to send me a SMALL workbook outlining your problem and giving
FULL DETAILS, I will take a look.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Thanks Don,
I could not get that to work. I did get it to work
if I eliminated the compound condition. IOW
{=sum(if(C2:C6>0,B2:B6/C2:C6))} worked
but adding a compound condition with the
"and" function, would not work. Perhaps that
is a limitation of the array formula.

Thanks again for your help.

Regards,
Doug Broad


insert>name>define
name it rngC
in the refers to box
=offset($c$1,0,0,counta($c:$c),1)
{=sum(if(and(isnumber(rngC),rngC>0)),rngB/rngC))}

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hello,

I have two columns that I would like to divide across and
sum the quotients. If the right column is not a number or is
0 or blank, I don't want to include it in the sum. Example:

A       B          C
1  Room  Area    Area/Person
2  Break 144.10  100
3  Office 157.70   50
4  Hall
5  Toilet
6  Stacks 785.00 100
....
Total Occupants:  ????

I would like to write a formula that would work with the
array and still work if I add rows or delete values.  Some rooms
are not applicable to the count but need to be included anyway.

Tried the array formula:
{=sum(if(and(isnumber(C1:C6),C1:C6>0)),B1:B6/C1:C6))}

but it did not work.  Sumproduct would work if I was trying
to multiply.  Anyone have any ideas?  Thanks.
[/QUOTE]
[/QUOTE]
[/QUOTE]

 

[Doug Broad]

Tom,
Thanks. That works. How did you figure that out?
Is there any place I can learn more about array formulas
other than the help files? If you don't have time to
answer, thanks again.

Regards,
Doug

=SUM((ISNUMBER(C1:C6))*(C1:C6>0)*IF(ISNUMBER(B1:B6/C1:C6),B1:B6/C1:C6))

Array entered will work

<snip>

 

[Tom Ogilvy]

The best place would be to read
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions

where innovative array formulas are offered up every day.

if you have a basic understanding of the operation and limitations of array
formulas, then it is just a matter of analyzing the problem.

Also, sumproduct is really an array formula - even though it doesn't have to
be array entered. It shares most of the limitations and constraints of
array entered formulas.

On constraint is that AND and OR don't work in array formulas for the most
part. Instead, you use Multiplication to perform AND operations and
summation to perform OR operations. Another problem is that an error result
in an element of the array will dominate the results, so you need to
explicitely screen these out like I did with the Divide by zero problem.

For Decisions and AND and OR, you basically want to end up with each
condition producing an array of 0 or 1 values. When these are multiplied
together, multiplying all 1's together, produces a 1 and anything else
produces a zero. This result can then be summed to produce a count. If you
want to produce selected values to be summed (such as you do), you can
produce an array of all values, and multiply by this array of zero or 1's to
nullify unwanted values (multiply by zero).

This is the basic approach.

You can build your formula in small parts and analyze it by going to the
formula bar, selecting complete subsets of your formula and doing F9. This
will evaluate the formula and show the array it is returning. You should
use a small range like 1 to 10 since it won't display long results. then
hit escape to replace the evaluated results with the original formula. Make
sure you have successfully entered the formula at least once before doing
this or hitting escape will clear the cell.

You can look at each subsection individually, then expand to combine
subsections to see how your formula is working.

Aladin Akyurek gave an explanation, not on array formulas per se, but on
using sumproduct and some related topics:

http://www.mrexcel.com/wwwboard/messages/8961.html

Chip Pearson has a discussion of array formulas

http://www.cpearson.com/excel/array.htm

 

[Doug Broad]

Tom,
Thank you for that excellent explanation and for the links.
I will watch the worksheet.functions newsgroup.
Thanks also to Chip Pearson and Aladin Akyurek for their
web explanations.

Regards,
Doug

The best place would be to read
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions

where innovative array formulas are offered up every day.

if you have a basic understanding of the operation and limitations of array
formulas, then it is just a matter of analyzing the problem.

<snip>