Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a countif | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |