IF Formula for the data

[Mangesharalkar]

Hi Team, I am looking for If formula fir movement of Data from week to week Eg If Week "I" move to "P-W" it should show 20% and so on

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Week1[/TD]
[TD="class: xl65, width: 64"]Week2[/TD]
[TD="class: xl65, width: 64"]%[/TD]
[/TR]
[TR]
[TD="class: xl66"]I[/TD]
[TD="class: xl66"]P-W[/TD]
[TD="class: xl67, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl66"]I[/TD]
[TD="class: xl66"]P-H[/TD]
[TD="class: xl67, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl66"]P-W[/TD]
[TD="class: xl66"]I[/TD]
[TD="class: xl67, align: right"]-20%[/TD]
[/TR]
[TR]
[TD="class: xl66"]P-W[/TD]
[TD="class: xl66"]P-H[/TD]
[TD="class: xl67, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl66"]P-W[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl67, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl66"]P-H[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl67, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]K-C[/TD]
[TD="class: xl67, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]CC[/TD]
[TD="class: xl67, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]K-R[/TD]
[TD="class: xl67, align: right"]-20%[/TD]
[/TR]
</tbody>[/TABLE]

 

[JaccoVis90]

I would work with a VLOOKUP function. Copy this table on an extra sheet and make an extra column in between the week 2 and % with the function = A2&B2 (should return as IP-W
in the original sheet =VLOOKUP(C2,extrasheet!$C$2:$D$10,2,0)

Or use names for the range on the extra sheet

 

[Mangesharalkar]

I am looking for movement on week on week basis if in Week 1 if line item is "I" in next week it become "P-W" it should show 20% as movement . V look up in not helping in it

If in Week 2 it is Moved from I to P-W (If I do v look up by it it will not help) it will give me error)
[TABLE="width: 252"]
<tbody>[TR]
[TD="class: xl67, width: 63"]Week1[/TD]
[TD="class: xl67, width: 63"]Week2[/TD]
[TD="class: xl67, width: 63"]CC[/TD]
[TD="class: xl67, width: 63"]%[/TD]
[/TR]
[TR]
[TD="class: xl68"]I[/TD]
[TD="class: xl68"]P-W[/TD]
[TD="class: xl68"]IP-W[/TD]
[TD="class: xl69, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl68"]I[/TD]
[TD="class: xl68"]P-H[/TD]
[TD="class: xl68"]IP-H[/TD]
[TD="class: xl69, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl68"]P-W[/TD]
[TD="class: xl68"]I[/TD]
[TD="class: xl68"]P-WI[/TD]
[TD="class: xl69, align: right"]-20%[/TD]
[/TR]
[TR]
[TD="class: xl68"]P-W[/TD]
[TD="class: xl68"]P-H[/TD]
[TD="class: xl68"]P-WP-H[/TD]
[TD="class: xl69, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl68"]P-W[/TD]
[TD="class: xl68"]K[/TD]
[TD="class: xl68"]P-WK[/TD]
[TD="class: xl69, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl68"]P-H[/TD]
[TD="class: xl68"]K[/TD]
[TD="class: xl68"]P-HK[/TD]
[TD="class: xl69, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl68"]K[/TD]
[TD="class: xl68"]K-C[/TD]
[TD="class: xl68"]KK-C[/TD]
[TD="class: xl69, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl68"]K[/TD]
[TD="class: xl68"]CC[/TD]
[TD="class: xl68"]KCC[/TD]
[TD="class: xl69, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl68"]K[/TD]
[TD="class: xl68"]K-R[/TD]
[TD="class: xl68"]KK-R[/TD]
[TD="class: xl69, align: right"]-20%[/TD]
[/TR]
[TR]
[TD="class: xl68"]I[/TD]
[TD="class: xl68"]I[/TD]
[TD="class: xl68"]II[/TD]
[TD="class: xl69, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl68"]P-W[/TD]
[TD="class: xl68"]P-W[/TD]
[TD="class: xl68"]P-WP-W[/TD]
[TD="class: xl69, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl68"]P-H[/TD]
[TD="class: xl68"]P-H[/TD]
[TD="class: xl68"]P-HP-H[/TD]
[TD="class: xl69, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl68"]K[/TD]
[TD="class: xl68"]K[/TD]
[TD="class: xl68"]KK[/TD]
[TD="class: xl69, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl68"]K-C[/TD]
[TD="class: xl68"]K-C[/TD]
[TD="class: xl68"]K-CK-C[/TD]
[TD="class: xl69, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl66"]CC[/TD]
[TD="class: xl66"]CC[/TD]
[TD="class: xl66"]CCCC[/TD]
[TD="class: xl69, align: right"]0%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 836"]
<colgroup><col span="4"><col span="4"></colgroup><tbody>[TR]
[TD]Week 1 account movement[/TD]
[TD]Week 2 account movement[/TD]
[TD]Week 3 account movement[/TD]
[TD]Week 4 account movement[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]P-W[/TD]
[TD]P-W[/TD]
[TD] P-W [/TD]
[TD] P-W [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] P-W [/TD]
[TD] P-W [/TD]
[TD] P-W [/TD]
[TD] P-W [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] I [/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] I [/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] I [/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] I [/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] K [/TD]
[TD] K [/TD]
[TD] K [/TD]
[TD] K [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] I [/TD]
[TD]I[/TD]
[TD]I[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] P-W [/TD]
[TD] P-W [/TD]
[TD] P-W [/TD]
[TD] P-W [/TD]
[/TR]
</tbody>[/TABLE]