Formula to choose a column between Plan Q1, Actual Q1 and then similarly for Plan Q2, Actual Q2 and so on Plan Q3, Act.. with two more conditions in m

[ronin83]

Hi all

To make it simple (Actual Q2,Q3,Q4 have zero values)
The data looks like:

Plan Q1 Actual Q1 Plan Q2 Actual Q2 Plan Q3 Actual Q3 Plan Q4 Actual Q4
11;7;7;0;6;0;6;0
2;3;5;0;4;0;6;0
2;6;6;0;6;0;4;0
3;2;1;0;2;0;5;0


Conditions for the formula:
1) If there is data in Actual Column, then choose it over Plan
2) If not, then choose data from Plan

Finally, we need a summation for the all the four columns.

Thanks
Ronin

 

[0verride]

Hi all

To make it simple (Actual Q2,Q3,Q4 have zero values)
The data looks like:

Plan Q1 Actual Q1 Plan Q2 Actual Q2 Plan Q3 Actual Q3 Plan Q4 Actual Q4
11;7;7;0;6;0;6;0
2;3;5;0;4;0;6;0
2;6;6;0;6;0;4;0
3;2;1;0;2;0;5;0


Conditions for the formula:
1) If there is data in Actual Column, then choose it over Plan
2) If not, then choose data from Plan

Finally, we need a summation for the all the four columns.

Thanks
Ronin

I think you mean this:
=If(B1<>0;B1;A1)+If(D1<>0;D1;C1)+If(F1<>0;F1;E1)+If(H1<>0;H1;G1)

This is just for the first row assuming you have them placed in Cells A1 through G1

 

[ronin83]

@ thanks Override.

Yes, that right. But is it possible to have a common columnwise approach for e.g. If (Actual > 0; Actual; Plan) for every alternative two columns?

For others,

I mean in the above example i would like to add the columns Actual Q1 + Plan Q2 + Plan Q3 + Plan Q4 and would like to have a general formula
which is If (Actual > 0; Actual; Plan), for every two columns