Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.


Regards,

RF

As your formula steps through the array, you will always be looking at
the same row, so it can't be both odd and even. I think you will need
to do something like:

=SUMPRODUCT((IF(MOD(ROW(D2:D62),2)=1,D2:D62,0)*IF( MOD(ROW(B3:B63),
2)=0,B3:B*63,0)))

This should multiply D2 with B3, D4 with B5 etc.

Hope this helps.

Pete

On Jul 4, 8:28 pm, RF wrote:
Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B*63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.

Regards,

RF

Hi Pete,

Thanks for your answer. I just tried it and I keep getting "0". I am quite
sure the first IF for the even rows is always returning 0.

Regards,

RF

"Pete_UK" wrote:

As your formula steps through the array, you will always be looking at
the same row, so it can't be both odd and even. I think you will need
to do something like:

=SUMPRODUCT((IF(MOD(ROW(D2:D62),2)=1,D2:D62,0)*IF( MOD(ROW(B3:B63),
2)=0,B3:B-63,0)))

This should multiply D2 with B3, D4 with B5 etc.

Hope this helps.

Pete

On Jul 4, 8:28 pm, RF wrote:
Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B-63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.

Regards,

RF

Try:

=SUMPRODUCT((MOD(ROW(D3:D64),2)=1)*D3:D64*(MOD(ROW (B2:B63),2)=0)*B2:B63)

"RF" wrote:

Hi Pete,

Thanks for your answer. I just tried it and I keep getting "0". I am quite
sure the first IF for the even rows is always returning 0.

Regards,

RF

"Pete_UK" wrote:

As your formula steps through the array, you will always be looking at
the same row, so it can't be both odd and even. I think you will need
to do something like:

=SUMPRODUCT((IF(MOD(ROW(D2:D62),2)=1,D2:D62,0)*IF( MOD(ROW(B3:B63),
2)=0,B3:B-63,0)))

This should multiply D2 with B3, D4 with B5 etc.

Hope this helps.

Pete

On Jul 4, 8:28 pm, RF wrote:
Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B-63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.

Regards,

RF

Try the amendment - it works okay for me.

Pete

On Jul 4, 8:52 pm, RF wrote:
Hi Pete,

Thanks for your answer. I just tried it and I keep getting "0". I am quite
sure the first IF for the even rows is always returning 0.

Regards,

RF



"Pete_UK" wrote:
As your formula steps through the array, you will always be looking at
the same row, so it can't be both odd and even. I think you will need
to do something like:

=SUMPRODUCT((IF(MOD(ROW(D2:D62),2)=1,D2:D62,0)*IF( MOD(ROW(B3:B63),
2)=0,B3:B-63,0)))

This should multiply D2 with B3, D4 with B5 etc.

Hope this helps.

Pete

On Jul 4, 8:28 pm, RF wrote:
Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B*-63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.

Regards,

RF- Hide quoted text -

- Show quoted text -

Sorry, I think you will need this:

=SUMPRODUCT((IF(MOD(ROW(D3:D63),2)=1,D3:D63,0)*IF( MOD(ROW(B2:B62),
2)=0,B2:B*62,0)))

This should give you D3*B2 + D5*B4 + D7*B6 etc.

Hope this helps.

Pete

On Jul 4, 8:44 pm, Pete_UK wrote:
As your formula steps through the array, you will always be looking at
the same row, so it can't be both odd and even. I think you will need
to do something like:

=SUMPRODUCT((IF(MOD(ROW(D2:D62),2)=1,D2:D62,0)*IF( MOD(ROW(B3:B63),
2)=0,B3:B*63,0)))

This should multiply D2 with B3, D4 with B5 etc.

Hope this helps.

Pete

On Jul 4, 8:28 pm, RF wrote:



Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B**63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.

Regards,

RF- Hide quoted text -

- Show quoted text -

Got it.

Thanks Pete.

"Pete_UK" wrote:

Sorry, I think you will need this:

=SUMPRODUCT((IF(MOD(ROW(D3:D63),2)=1,D3:D63,0)*IF( MOD(ROW(B2:B62),
2)=0,B2:B-62,0)))

This should give you D3*B2 + D5*B4 + D7*B6 etc.

Hope this helps.

Pete

On Jul 4, 8:44 pm, Pete_UK wrote:
As your formula steps through the array, you will always be looking at
the same row, so it can't be both odd and even. I think you will need
to do something like:

=SUMPRODUCT((IF(MOD(ROW(D2:D62),2)=1,D2:D62,0)*IF( MOD(ROW(B3:B63),
2)=0,B3:B-63,0)))

This should multiply D2 with B3, D4 with B5 etc.

Hope this helps.

Pete

On Jul 4, 8:28 pm, RF wrote:



Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B--63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.

Regards,

RF- Hide quoted text -

- Show quoted text -

You're welcome, but be aware that if you have a space in one of the
cells in column B that are meant to be blank then you will get the
#VALUE error.

Pete

On Jul 4, 9:08 pm, RF wrote:
Got it.

Thanks Pete.



"Pete_UK" wrote:
Sorry, I think you will need this:

=SUMPRODUCT((IF(MOD(ROW(D3:D63),2)=1,D3:D63,0)*IF( MOD(ROW(B2:B62),
2)=0,B2:B-62,0)))

This should give you D3*B2 + D5*B4 + D7*B6 etc.

Hope this helps.

Pete

On Jul 4, 8:44 pm, Pete_UK wrote:
As your formula steps through the array, you will always be looking at
the same row, so it can't be both odd and even. I think you will need
to do something like:

=SUMPRODUCT((IF(MOD(ROW(D2:D62),2)=1,D2:D62,0)*IF( MOD(ROW(B3:B63),
2)=0,B3:B-63,0)))

This should multiply D2 with B3, D4 with B5 etc.

Hope this helps.

Pete

On Jul 4, 8:28 pm, RF wrote:

Hello,

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B*--63,0))) but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Thanks in advance.

Regards,

RF- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the
"real" one.

Let's say I wanted to multiply the odd rows from D2:D63 * even rows from
B2:B63, then add the results. I have tried things like
=SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0)))
but it won't work, it will always display 0 as a result.

Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real"
progress on odd rows and "planned" progress on even rows.

I know it shouldn't be so difficult, but I'm stuck.

Hmm! I have been answering your question over in m.p.e.misc group while you
are running it here also. Something for you to consider for the future...

From a post by Jeff Johnson:

"You have posted this question individually to multiple groups. This is
called Multiposting and it's BAD. Replies made in one group will not be
visible in the other groups, which may cause multiple people to respond to
your question with the same answer because they didn't know someone else had
already done it. This is a waste of time.

If you MUST post your message to multiple groups, post a single message and
select all the groups (or type their names manually in the Newsgroups field,
separated by commas) in which you want it to be seen. This is called
Crossposting and when used properly it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer answering
questions on newsgroups do see it as a problem. You can't imagine how
annoying it is for a volunteer to read a question, research background
material, test sample code and then formulate and post an answer to the
original question only to go to another newsgroup and find the question
posted and ALREADY answered over there. On top of that, if you cross-post
your question, all of the readers in all the newsgroups it is cross-posted
to get to see both the original question and all of the answers given to it.
This is beneficial to you because then we can add additional material to,
add clarification to, as well as add additional examples to an answer you
have received previously... that means you end up with a more complete
solution to your problem. This is a win-win situation for all of us."

Rick