Sum IF.....

[Jamie Wagler]

On this page I am having trouble with the N/C ATP calculation. (Row8)

What I am trying to attempt is to calculate the total sum of MAX(Row4 & Row5) where there is no entry in Row6 until the following entry in Row6 along with the value of N/C ATP in the previous period. In the first period it pulls from B5

 

[NARAYANK991]

Hi Jamie ,

I am having trouble understanding your requirement.

Cells E6 and H6 are not blank , while others in row 6 are blank ; the calculation of N/C ATP in E8 and H8 will be different from the calculation of N/C ATP in other cells in row 8.

Can you take the example of columns F , G , I ,... and say what should be the values in F8 , G8 , I8 ,... ? Explain how you have arrived at the output.

Narayan

 

[Jamie Wagler]

@NARAYANK991

Row6 is a manual input, which is why E6 and H6 are not blank. What I would like to do is to have the sum of MAX(Row4 & Row5) calculated between inputs so that when there is not an input in Row6 it calculates the total max sum until the next input.

Does that help you out any?

 

[Jake Collins]

Dear Jamie

To help understand things can you answer the following questions:

Should F8 have the value 160 (the highest of F4 and F5)?
Should G8 have the value 340 (=160 +180 the max of f4 and f5 plus the max of g4 and g5)?
What value (and why) should H8 have?

 

[Jamie Wagler]

@Jake Collins

F8 should have the combined sum of MAX(F4:F5) - F6, G8 = MAX(G4:G5) - G6 etc etc.
Added to that I require that IF F6 is 0 or no entry that G8 Sums the MAX for F4:F5 and G4:G5 - G6 and that this calculation would compensate no matter how many spaces in ROW6 are empty from Column E to Column P.

Does that help any?

 

[Jamie Wagler]

One last note... I only need this to cover 3 consecutive periods. It does not have to continue expanding. IE there will never be more than 3 periods without content.