Problem with IF statement

[Tanya]

Hi, I hope someone may be able to help me.

I have the following formula which works fine, however the values in P5, R5,
T5, V5, X5, Z5, AB5 AND AD5 may contain text which is messing up my formula.
My reasoning behind needing to do this is calculating student marks over
several tasks, with different % weights and until the assessment task has
been set I have an IF formula based on the false being "-".

I have tried the =IF(ISNUMBER(P5),(P5*$O$3),0,IF(ISNUMBER(R5),(R5*$Q$3),0,....
and getting all kinds of error messages, in cluding too many augments.

=((P5*$O$3)+(R5*$Q$3)+(T5*$S$3)+(V5*$U$3)+(X5*$W$3)+(Z5*$Y$3)+(AB5*$AA$3)+(AD5*$AC$3))*0.01

I would appreciate any assistance here.

Thanks
Tanya

 

[Toppers]

try:

=SUMPRODUCT(($O$3:$AC$3),(P5:AD5))*0.01

HTH

 

[Tanya]

Thank you for responding so quickly to my delema.

Unfortunately this doesn't solve my problem. I'm not sure if perhaps I am
making this too complicated for myself. My sheet has multiple functions
happening all over the place. I will try to explain a little clearer.

O3 has a value=15% [this is the task weight for Task1]

Q3 has the task weight of =10% however Task2 has not been undertaken yet and
no mark awarded

P6 contains the result of Task 1 which has been converted to a number out of
100

R6 contains "-" because no mark has been awarded yet i.e. neither 0 or any
other number can be awarded yet.

In cell AE5 I need to multiply P6 by O3, [this gives me the correct weight
for the mark] then add next task IF ISNUMBER and finally multiple the result
by 0.01 to get a percentage out of 100.

I have only listed two tasks to simplify my problem, there are actually 8
tasks in total, same scenaria, requiring each task to be multipled by its
weight then finally multipled by 0.01

If you don't think this is possible and could recommend another way around
this I would appreciate you feedback.

Thanking you in advance

Tanya

 

[Ron Rosenfeld]

Hi, I hope someone may be able to help me.

I have the following formula which works fine, however the values in P5, R5,
T5, V5, X5, Z5, AB5 AND AD5 may contain text which is messing up my formula.
My reasoning behind needing to do this is calculating student marks over
several tasks, with different % weights and until the assessment task has
been set I have an IF formula based on the false being "-".

I have tried the =IF(ISNUMBER(P5),(P5*$O$3),0,IF(ISNUMBER(R5),(R5*$Q$3),0,....
and getting all kinds of error messages, in cluding too many augments.

=((P5*$O$3)+(R5*$Q$3)+(T5*$S$3)+(V5*$U$3)+(X5*$W$3)+(Z5*$Y$3)+(AB5*$AA$3)+(AD5*$AC$3))*0.01

I would appreciate any assistance here.

Thanks
Tanya

I note that the data is in every other column, with row 5 offset by 1 column
from row 3.

SUMPRODUCT will ignore text. So if there is no numeric data in the intervening
columns, you could use this formula:

=SUMPRODUCT($O$3:$AC$3,P5:AD5)


--ron

 

[Toppers]

Try this which takes alternative columns: columns with "-" are treated as
zero (as per your IF statement).

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

If cells Q5,S5 etc are blank or zero, the first formula would work.

In your original posting your cells were in row 5, but your second said row
6, with answer in AE5: very confusing!

Why did you say the first one didn't work?

 

[Toppers]

See Ron's reply: same formula as my original.

Thank you for responding so quickly to my delema.

Unfortunately this doesn't solve my problem. I'm not sure if perhaps I am
making this too complicated for myself. My sheet has multiple functions
happening all over the place. I will try to explain a little clearer.

O3 has a value=15% [this is the task weight for Task1]

Q3 has the task weight of =10% however Task2 has not been undertaken yet and
no mark awarded

P6 contains the result of Task 1 which has been converted to a number out of
100

R6 contains "-" because no mark has been awarded yet i.e. neither 0 or any
other number can be awarded yet.

In cell AE5 I need to multiply P6 by O3, [this gives me the correct weight
for the mark] then add next task IF ISNUMBER and finally multiple the result
by 0.01 to get a percentage out of 100.

I have only listed two tasks to simplify my problem, there are actually 8
tasks in total, same scenaria, requiring each task to be multipled by its
weight then finally multipled by 0.01

If you don't think this is possible and could recommend another way around
this I would appreciate you feedback.

Thanking you in advance

Tanya

try:

=SUMPRODUCT(($O$3:$AC$3),(P5:AD5))*0.01

HTH

 

[Tanya]

Thank you for your rseponse,

Unfortunately there are values in other columns on rows 3 and 5 that I need
to ignore

 

[Toppers]

Try:

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

Or see my other posting.

 

[Tanya]

Thank you very very much, it worked wonderfully.

I am going to study this formula tomorrow, as I haven't come across MOD or
the other variation.

I want you to know I really appreciate your patience in working this out for
me.

Kind Regards
Tanya

 

[Toppers]

Enjoy the rest of the weekend and thank you for the feedback.