COUNTIF
 

Hi
I have the following formula, which I this forum gave me about six months
ago. The problem is I have just realised it is dependent on a couple of
factors.
1. If I delete or add a column before the group of columns I am working on
the formula returns an error.
2. I have just realised that if the first column in the aray does not have
an entry the whole formula does not work.

While I am grateful to the effort afforded my project already, I would
greatly appreciate if someone could look at my problem
Cell Reference AE5 has the following formula
=IF(COUNT(O5:AB5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P5:AD5),2)=0),(P5:AD5))*0.01)

What I need to do is as follows:

In columns O to AC row 3 alternate cells there is a % value
In columns P to AD Row 5-34 in alternate cells the content is either "-" or
value between 0-100

Therefore cell AE5 needs to calculate P5*O3 + R5*Q3 and so on...

I pray there is an easier formula to work with because I just can't get the
formula to work properly, every time I want to add a column to the left area
I am working in I get errors.

Please help

Regards
Tanya

COUNTIF
 

In looking deeper at this problem, I have come up with the following formula

=IF(ISNUMBER(P5*$O$3+R5*$Q$3+T5*$S$3+V5*$U$3+X5*$W $3+Z5*$Y$3+AB5*$AA$3+AD5*$AC$3),(P5*$O$3+R5*$Q$3+T 5*$S$3+V5*$U$3+X5*$W$3+Z5*$Y$3+AB5*$AA$3+AD5*$AC$3 )*0.01,"-")

However it only returns me the value "-"
????????

"Tanya" wrote:

Hi
I have the following formula, which I this forum gave me about six months
ago. The problem is I have just realised it is dependent on a couple of
factors.
1. If I delete or add a column before the group of columns I am working on
the formula returns an error.
2. I have just realised that if the first column in the aray does not have
an entry the whole formula does not work.

While I am grateful to the effort afforded my project already, I would
greatly appreciate if someone could look at my problem
Cell Reference AE5 has the following formula
=IF(COUNT(O5:AB5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P5:AD5),2)=0),(P5:AD5))*0.01)

What I need to do is as follows:

In columns O to AC row 3 alternate cells there is a % value
In columns P to AD Row 5-34 in alternate cells the content is either "-" or
value between 0-100

Therefore cell AE5 needs to calculate P5*O3 + R5*Q3 and so on...

I pray there is an easier formula to work with because I just can't get the
formula to work properly, every time I want to add a column to the left area
I am working in I get errors.

Please help

Regards
Tanya

COUNTIF
 

For the SUMPRODUCT part of the formula, try...

=SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3)-COLUMN($O$3),2)=0),$O$3:$AC$3,P5:AD
5)*0.01

For the logical test, assuming that there are no numerical values
between the target cells, do you mean...?

COUNT(P5:AD5)=0

Hope this helps!

In article ,
Tanya wrote:

Hi
I have the following formula, which I this forum gave me about six months
ago. The problem is I have just realised it is dependent on a couple of
factors.
1. If I delete or add a column before the group of columns I am working on
the formula returns an error.
2. I have just realised that if the first column in the aray does not have
an entry the whole formula does not work.

While I am grateful to the effort afforded my project already, I would
greatly appreciate if someone could look at my problem
Cell Reference AE5 has the following formula
=IF(COUNT(O5:AB5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3
),--(MOD(COLUMN(P5:AD5),2)=0),(P5:AD5))*0.01)

What I need to do is as follows:

In columns O to AC row 3 alternate cells there is a % value
In columns P to AD Row 5-34 in alternate cells the content is either "-" or
value between 0-100

Therefore cell AE5 needs to calculate P5*O3 + R5*Q3 and so on...

I pray there is an easier formula to work with because I just can't get the
formula to work properly, every time I want to add a column to the left area
I am working in I get errors.

Please help

Regards
Tanya

COUNTIF
 

Thank you greatly, Domenic
It worked wonderfully, one day I love to work out all the different parts of
this formula.
Kind Regards
Tanya

"Domenic" wrote:

For the SUMPRODUCT part of the formula, try...

=SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3)-COLUMN($O$3),2)=0),$O$3:$AC$3,P5:AD
5)*0.01

For the logical test, assuming that there are no numerical values
between the target cells, do you mean...?

COUNT(P5:AD5)=0

Hope this helps!

In article ,
Tanya wrote:

Hi
I have the following formula, which I this forum gave me about six months
ago. The problem is I have just realised it is dependent on a couple of
factors.
1. If I delete or add a column before the group of columns I am working on
the formula returns an error.
2. I have just realised that if the first column in the aray does not have
an entry the whole formula does not work.

While I am grateful to the effort afforded my project already, I would
greatly appreciate if someone could look at my problem
Cell Reference AE5 has the following formula
=IF(COUNT(O5:AB5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3
),--(MOD(COLUMN(P5:AD5),2)=0),(P5:AD5))*0.01)

What I need to do is as follows:

In columns O to AC row 3 alternate cells there is a % value
In columns P to AD Row 5-34 in alternate cells the content is either "-" or
value between 0-100

Therefore cell AE5 needs to calculate P5*O3 + R5*Q3 and so on...

I pray there is an easier formula to work with because I just can't get the
formula to work properly, every time I want to add a column to the left area
I am working in I get errors.

Please help

Regards
Tanya