How to extract calculation of values among ranges ignoring empty cells?

[deputat_x]

Dear All, I have another one interesting case I need to solve and ask yours assistance. That is actually the follow up of my previous problem, which I published a few days ago which were solved with high priority by one of Excel master experts.
Below you can see Table with data:
[TABLE="width: 600, align: center"]
<tbody>[TR]
[TD](A1):
Item Premium Category
[/TD]
[TD](B1)
Item Category 1
[/TD]
[TD](C1)
Item Category 2
[/TD]
[TD](D1)
Item Category 3
[/TD]
[TD](E1)
Type of Character
[/TD]
[TD](F1)
Number assigned to Character
[/TD]
[TD](G1)
Sum of contiguous Character appearance
[/TD]
[TD](H1)
Range
[/TD]
[/TR]
[TR]
[TD]A2: 330
[/TD]
[TD]B2: 100
[/TD]
[TD]C2:200
[/TD]
[TD]D2: 50
[/TD]
[TD]E2: Neutral
[/TD]
[TD]F2: 0
[/TD]
[TD]G2: 1
[/TD]
[TD]H2:280
[/TD]
[/TR]
[TR]
[TD]A3: 300
[/TD]
[TD]B3: 120
[/TD]
[TD]C3:210
[/TD]
[TD]D3: 60
[/TD]
[TD]E3: Good
[/TD]
[TD]F3: 1
[/TD]
[TD]G3
[/TD]
[TD]H3[/TD]
[/TR]
[TR]
[TD]A4: 310
[/TD]
[TD]B4: 110
[/TD]
[TD]C4:220
[/TD]
[TD]D4: 70
[/TD]
[TD]E4: Good
[/TD]
[TD]F4: 1
[/TD]
[TD]G4: 2
[/TD]
[TD]H4:230
[/TD]
[/TR]
[TR]
[TD]A5: 320
[/TD]
[TD]B5: 150
[/TD]
[TD]C5:240
[/TD]
[TD]D5: 10
[/TD]
[TD]E5: Bad
[/TD]
[TD]F5: -1
[/TD]
[TD]G5
[/TD]
[TD]H5
[/TD]
[/TR]
[TR]
[TD]A6: 340
[/TD]
[TD]B6: 115
[/TD]
[TD]C6:255
[/TD]
[TD]D6: 20
[/TD]
[TD]E6: Bad
[/TD]
[TD]F6: -1
[/TD]
[TD]G6
[/TD]
[TD]H6
[/TD]
[/TR]
[TR]
[TD]A7: 350
[/TD]
[TD]B7: 160
[/TD]
[TD]C7:290
[/TD]
[TD]D7: 35
[/TD]
[TD]E7: Bad
[/TD]
[TD]F7: -1
[/TD]
[TD]G7: -3
[/TD]
[TD]H7:-285
[/TD]
[/TR]
[TR]
[TD]A8: 360
[/TD]
[TD]B8: 170
[/TD]
[TD]C8:295
[/TD]
[TD]D8: 40
[/TD]
[TD]E8: Good
[/TD]
[TD]F8: 1
[/TD]
[TD]G8: 1
[/TD]
[TD]H8:320
[/TD]
[/TR]
[TR]
[TD]A9: 345
[/TD]
[TD]B9: 105
[/TD]
[TD]C9:235
[/TD]
[TD]D9: 80
[/TD]
[TD]E9: Bad
[/TD]
[TD]F9: -1
[/TD]
[TD]G9
[/TD]
[TD]H9
[/TD]
[/TR]
[TR]
[TD]A10: 370
[/TD]
[TD]B10:185
[/TD]
[TD]C10:245
[/TD]
[TD]D10:90
[/TD]
[TD]E10: Bad
[/TD]
[TD]F10: -1
[/TD]
[TD]G10: -2
[/TD]
[TD]H10:-255
[/TD]
[/TR]
[TR]
[TD]A11: 380
[/TD]
[TD]B11:155
[/TD]
[TD]C11:250
[/TD]
[TD]D11:95
[/TD]
[TD]E11: Good
[/TD]
[TD]F11: 1
[/TD]
[TD]G11
[/TD]
[TD]H11
[/TD]
[/TR]
[TR]
[TD]A12: 390
[/TD]
[TD]B12:165
[/TD]
[TD]C12:275
[/TD]
[TD]D12:65
[/TD]
[TD]E12: Good
[/TD]
[TD]F12: 1
[/TD]
[TD]G12: 2
[/TD]
[TD]H12:315
[/TD]
[/TR]
[TR]
[TD]A13: 355
[/TD]
[TD]B13:145
[/TD]
[TD]C13:270
[/TD]
[TD]D13:55
[/TD]
[TD]E13: Neutral
[/TD]
[TD]F13: 0
[/TD]
[TD]G13: 1
[/TD]
[TD]H13:300
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Tips to solve:

1. H2 : “H2 looks through whole range G2:G13, then see in G2 value equal to 1 and returns value based on formula (A2-D2)”;
2. H3 : “H3 looks through whole range G2:G13 then finds cell G3 is empty, leaves without changes”;
3. H4 : “H4 looks through whole range G2:G13 then finds cell G4 is 2 and returns value based on formula (A3-D4)”;
4. H5 : “H5 looks through whole range G2:G13 then finds cell G5 is empty, leaves without changes”;
5. H6 : “H6 looks through whole range G2:G13 then finds cell G6 is empty, leaves without changes”;
6. H7 : “H7 looks through whole range G2:G13 then finds cell G7 is -3 and returns value based on formula (D7-A5)”;
7. H8 : “H8 looks through whole range G2:G13, then see in G8 value equal to 1 and returns value based on formula (A8-D8)”;
8. H9 : “H9 looks through whole range G2:G13 then finds cell G9 is emty, leaves without changes”;
9. H10 : “H10 looks through whole range G2:G13, then see in G10 value equal to -2 and returns value based on formula (D10-A9)”;
10. H11 : “H11 looks through whole range G2:G13 then finds cell G11 is emty, leaves without changes;
11. H12 : “H12 looks through whole range G2:G13, then see in G12 value equal to 2 and returns value based on formula (A11-D12)”;
12. H13: “H13 looks through whole range G2:G13, then see in G13 value equal to 1 and returns value based on formula (A13-D13)”

Hope on your favor.
Kind regards
Robin

 

[sanrv1f]

In H2

=CHOOSE(SIGN(G2)+2,D2-A2,"",A2-D2)

and fill down

 

[deputat_x]

No, your way do not take into account range of rows it counts only single row. If you count your way the results in Column H are other than mine in the table above

 

[deputat_x]

In H2

=CHOOSE(SIGN(G2)+2,D2-A2,"",A2-D2)

No, your way do not take into account range of rows it counts only single row. If you count your way the results in Column H are other than mine in the table above
and fill down

 

[István Hirsch]

Cells H3, H5, H6 etc. have a value that should be kept, or can they be made empty?

 

[godsaaint]

Insert in H2 and fill down:

=IF(ISBLANK(G2),"",IF(SIGN(G2)=1,INDIRECT("A"&(ROW(A2)+1-G2))-D2,D2-INDIRECT("A"&(ROW(A2)+1+G2))))

 

[deputat_x]

Insert in H2 and fill down:

=IF(ISBLANK(G2),"",IF(SIGN(G2)=1,INDIRECT("A"&(ROW(A2)+1-G2))-D2,D2-INDIRECT("A"&(ROW(A2)+1+G2))))

Dear friend, many many thanks to you . Your code works excellent!