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:

(A1): Item Premium Category	(B1) Item Category 1	(C1) Item Category 2	(D1) Item Category 3	(E1) Type of Character	(F1) Number assigned to Character	(G1) Sum of contiguous Character appearance	(H1) Range
A2: 330	B2: 100	C2:200	D2: 50	E2: Neutral	F2: 0	G2: 1	H2:280
A3: 300	B3: 120	C3:210	D3: 60	E3: Good	F3: 1	G3	H3
A4: 310	B4: 110	C4:220	D4: 70	E4: Good	F4: 1	G4: 2	H4:230
A5: 320	B5: 150	C5:240	D5: 10	E5: Bad	F5: -1	G5	H5
A6: 340	B6: 115	C6:255	D6: 20	E6: Bad	F6: -1	G6	H6
A7: 350	B7: 160	C7:290	D7: 35	E7: Bad	F7: -1	G7: -3	H7:-285
A8: 360	B8: 170	C8:295	D8: 40	E8: Good	F8: 1	G8: 1	H8:320
A9: 345	B9: 105	C9:235	D9: 80	E9: Bad	F9: -1	G9	H9
A10: 370	B10:185	C10:245	D10:90	E10: Bad	F10: -1	G10: -2	H10:-255
A11: 380	B11:155	C11:250	D11:95	E11: Good	F11: 1	G11	H11
A12: 390	B12:165	C12:275	D12:65	E12: Good	F12: 1	G12: 2	H12:315
A13: 355	B13:145	C13:270	D13:55	E13: Neutral	F13: 0	G13: 1	H13:300

<tbody>
</tbody>

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!